CODEWITHSUNDEEP
sql-servert-sqlsumsubquerycase
Elizabeth
Elizabeth

Reputation: 765

SQL - Summing aggregate sub query

This is the code I have

select  distinct sli.order_no,
            sli.pkg_no,
            case when line.primary_ind = 'Y' then sum(paid_amt) else 0 end  as paid_amt,
            line.pkg_li_no,     
            sum(case when sli.perf_no = 0 then 1 else 0 end) as num_seats_pur,
            sli.status
    from    t_sub_line sli
    left outer join t_line line on sli.li_seq_no = line.li_seq_no
    where sli.order_no in (1,2)
    group by
            sli.order_no,
            sli.pkg_no,
            line.primary_ind,
            line.pkg_li_no,
            sli.status  
    having  line.primary_ind = 'Y'

This code produces this output 

order_no pkg_no paid_amt    pkg_li_no   num_seats_pur   status
1        322    124.00      967          2              7
1        322    -124.00     992          2              4
2        854    253.00      952          1              7
2        854    -253.00     996          1              4

what I really need for the data to return is the following. I need the sum of paid_amt field. 

order_no pkg_no paid_amt    pkg_li_no   num_seats_pur   status
1        322    0       967              2              7
2        854    0       996              1              4

even if i change status to be max(status) so its not grouping on it. I don't have sum_paid amt.

when i try this code: 

sum(case when line.primary_ind = 'Y' then sum(paid_amt) else 0 end)  as paid_amt,

I get the following error message 

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Upvotes: 1

Views: 79

Answers (2)

H20rider
H20rider

Reputation: 2282

A couple things you need to do.

  1. Comment out pkg_li_no from the select and group by
  2. Change the having statement to be included in the where statement
  3. Remove the case from paid_amt since it is not necessary
  select  distinct sli.order_no,
                    sli.pkg_no,
                    sum(paid_amt)as paid_amt,
                  --  line.pkg_li_no,     
                    sum(case when sli.perf_no = 0 then 1 else 0 end) as num_seats_pur,
                    sli.status
            from    t_sub_line sli
            left join t_line line on sli.li_seq_no = line.li_seq_no
            where sli.order_no in (1,2)
            and  line.primary_ind = 'Y'
            group by
                    sli.order_no,
                    sli.pkg_no,
                    line.primary_ind,
                   -- line.pkg_li_no,
                    sli.status

Other notes: I am not sure you want to select or group by the pkg_no or status, but you know more what outcome are looking for. If they are different values, there will be different records.

Upvotes: 1

Kelevra
Kelevra

Reputation: 126

I'm not sure what you're trying to accomplish, but it seems that you overthinking the query. Try to simplifying it. FYI HAVING happens after the grouping, WHERE happens before the grouping, you don't even need the CASE

select  distinct sli.order_no,
            sli.pkg_no,
            sum(paid_amt) as paid_amt, 
            sum(case when sli.perf_no = 0 then 1 else 0 end) as num_seats_pur,
            sli.status
    from    t_sub_line sli
    left outer join t_line line on sli.li_seq_no = line.li_seq_no
    where sli.order_no in (1,2)
      and line.primary_ind = 'Y'
    group by
            sli.order_no,
            sli.pkg_no,
            sli.status

Upvotes: 0

Related Questions