CODEWITHSUNDEEP
sqlsql-server
Somasekhar Ghanta
Somasekhar Ghanta

Reputation: 81

Need to do an aggregate function inside a subquery by converting rows into columns

Here's input table:Account

accountnumber year   quarter    catgeory    owedamt  payedamt  writeoffamt
101           2001     1        Tax         130.0      0.0         30.0
101           2001     1        Interest    130.0      30.0        30.0
101           2001     2        Interest    120.0      20.0        20.0
102           2002     1        Interest    120.0      100.0       20.0
102           2002     2        Tax         110.0      100.0       10.0

output

accountnumber  year   quarter    Tax (outstanding)       Interest (outstanding)
101            2001     1           100.0                      70.0
101            2001     2            0.0                       80.0
102            2002     2            10.0                       0.0

Here "outstanding" is caluclated on the basis of sum(OWED_AMT - (PAID_AMT + WRITEOFF_AMT)) AS OUTSTANDING

Note: we need to have the columns as Tax and Interest but the amount in those columns should be outstanding which is calculated on the basis of above formula.

Note 2: for the accountntumber:102 ,Interest (outstanding) is calculated
sum(OWED_AMT - (PAID_AMT + WRITEOFF_AMT)) which is (120-(100+20))=0.0

But we dont have any Tax category in that year and the quarter so the output resulted 0.0

If we have Both Tax (outstanding) and Interest (outstanding) as zero in the same year and same quarter we can skip that row

Query i am using is :

with summaryData (accountnumber ,
            year ,
            quarter, tax, interest) as 
(
    SELECT  accountnumber ,
            year ,
            quarter ,
            SUM(CASE WHEN catgeory = 'Tax' THEN sum(OWED_AMT - (PAID_AMT + WRITEOFF_AMT))
                     ELSE 0
                END),
            SUM(CASE WHEN catgeory = 'Interest' THEN sum(OWED_AMT - (PAID_AMT + WRITEOFF_AMT))
                     ELSE 0
                END)
    FROM    acccount
    GROUP BY accountnumber ,
            year ,
            quarter
)
select * from summaryData
where tax > 0 or interest > 0;

The error i am getting here is :

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

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You don't need the nested aggregations in the CTE. So:

with summaryData (accountnumber, year, quarter, tax, interest) as (
      SELECT accountnumber, year, quarter,
             SUM(CASE WHEN catgeory = 'Tax' THEN OWED_AMT - (PAID_AMT + WRITEOFF_AMT)
                      ELSE 0
                 END),
             SUM(CASE WHEN catgeory = 'Interest' THEN OWED_AMT - (PAID_AMT + WRITEOFF_AMT)
                      ELSE 0
                 END)
      FROM acccount
      GROUP BY accountnumber, year, quarter
     )

Upvotes: 2

Related Questions