Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: i need help with a total!

    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

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    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
    Last edited by Damian Ibbotson; 02-04-04 at 11:05.

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    thanks for your help i will try that.

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    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.
    Last edited by Damian Ibbotson; 02-04-04 at 09:50.

  5. #5
    Join Date
    Feb 2004
    Posts
    4
    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.

  6. #6
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    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?

  7. #7
    Join Date
    Feb 2004
    Posts
    4
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •