i need help with a total!

Nick007VH · #1 (**permalink**) 02-03-04, 10:20

ok this is the ugliest sql in the history of man, i have to use sub selects here and a case statement, because i am trying to determine a total dollar amount for plan num's that are final, meaning that they have no null po amounts. Also, i'm more of a java programmer and pretty new to the finer points of sql. the result of the missingcase statement returns the right number, but i want to sum this. A running total, or just a grand total would work, as long as i can get the number. Oh, and this is on an AS400 and i can't use rollup or olap functions. Thanks to anyone who helps.

select distinct po.plan_num, po.agentid ,
(case when
(select count(plan_num) from alliance.po as po2 where po2.plan_num = po.plan_num and po2.agentid = po.agentid) >
(select count(plan_num) from alliance.po as po2 where po2.plan_num = po.plan_num and po2.agentid = po.agentid and po_amount is not null)

then (select sum(po_amount) from alliance.po as po2 where po2.plan_num = po.plan_num and po2.agentid = po.agentid) end) as missingsum

from alliance.po as po where po_amount is not null and agentid = 5084

Damian Ibbotson · #2 (**permalink**) 02-04-04, 05:40

You can do this without OLAP functions but it is messy and performance may be an issue.

I've assumed that you don't really want any null 'missing_sum' values, which your CASE statement would actually return. I've also removed 'agent_id' from the result set for clarity as you already appear to have this in your application(?).

The initial rewrite of your query looks like this...

Code:

select plan_num
,      sum(po_amount)
from   alliance.po po
where agentid = 5084
  and not exists (
                  select plan_num
                  from   alliance.po po2
                  where  po2.plan_num = po.plan_num
                    and  po2.agent_id = po.agent_id
                    and  po2.po_amount is null
                 )
group by plan_num
;

If you want to include a grand total, introduce a scalar sub-select in your SELECT statement...

Code:

select po.plan_num
,      sum(po_amount)
,      (
        select sum(po_amount)
        from   alliance.po po2
        where  agentid = 5084
          and  not exists (
                           select plan_num
                           from   alliance.po po2
                           where  po2.plan_num = po.plan_num
                             and  po2.agent_id = po.agent_id
                             and  po2.po_amount is null
                          )
       )
from   alliance.po po
where  agentid = 5084
  and  not exists (
                   select plan_num
                   from   alliance.po po2
                   where  po2.plan_num = po.plan_num
                     and  po2.agent_id = po.agent_id
                     and  po2.po_amount is null
                  )
group by po.plan_num
;

If you want to have a running total, self join to all rows where the joining field(s) has a lesser or equal value to the current row's (I've also had to nest the initial SELECT to aggregate the po_amount values) ...

Code:

select po1.plan_num
,      po1.po_amount
,      sum(po2.po_amount) agg_total
from   (
        select agent_id
        ,      plan_num
        ,      sum(po_amount) po_amount
        from   alliance.po po1
        where  agent_id = 5084
          and  not exists (
                           select plan_num
                           from   alliance.po po
                           where  po.plan_num = po1.plan_num
                             and  po.agent_id = po1.agent_id
                             and  po.po_amount is null
                          )
        group by agent_id, plan_num
       ) po1
,      alliance.po po2
where  po1.agent_id = po2.agent_id
  and  exists (
                   select plan_num
                   from   po1
                   where  po1.plan_num = po2.plan_num
                     and  po1.agent_id = po2.agent_id
                  )
  and  po1.plan_num >= po2.plan_num
group by po1.plan_num, po1.po_amount
;

These are all untested because I obviously don't have access to your data.

Damian

Nick007VH · #3 (**permalink**) 02-04-04, 08:08

thanks for your help i will try that.

Damian Ibbotson · #4 (**permalink**) 02-04-04, 08:47

Quote:

Originally posted by Nick007VH
thanks for your help i will try that.

Okay. Can I just clarify though...

You're introduction states that you are trying to get the amount for final plans (i.e. where there are no null amounts for a plan). You're query however, seems to want to sum up the amounts for plans that have null payments (i.e. the no. of payments including nulls > no. of payments not including nulls). Plus you are using the alias 'missing_sum' which makes me wonder what you really meant to ask.

I have attempted to do what your introduction asks for.

Nick007VH · #5 (**permalink**) 02-04-04, 09:12

you are right, i was looking for the sum, i didn't explain very well. Someone else here got the problem and created a view so that it could be as a normal sum upon the view, and there is no need for a case statement, all the logic is now in the where clause. It is slow, but works.

Damian Ibbotson · #6 (**permalink**) 02-04-04, 09:16

Quote:

Originally posted by Nick007VH
you are right, i was looking for the sum, i didn't explain very well. Someone else here got the problem and created a view so that it could be as a normal sum upon the view, and there is no need for a case statement, all the logic is now in the where clause. It is slow, but works.

But does that mean that you will create a view on the fly for every agent_id?

Can you post this solution?

Nick007VH · #7 (**permalink**) 02-04-04, 09:38

the view is static, but it will be accessed every time. here is what was used to create the view:

drop view alliance.po_enroll;
create view alliance.po_enroll as select distinct po.agentid, po.plan_num,
(select count(po_num) from alliance.po where plan_num = po.plan_num) as total_po,
(select count(po_num) from alliance.po where plan_num = po.plan_num and po_amount is not null) as complete_po,
(select sum(po_amount) from alliance.po where plan_num = po.plan_num) as po_amt_total,
dr.region
from alliance.po as po, alliance.distributors_region as dr
where po.plan_num = dr.plan_num;

Internet Services

Web Development

Digital Marketing

Consumer Tech