CODEWITHSUNDEEP
sqlsql-servert-sql
stackuser
stackuser

Reputation: 672

How to SUM in case statement

How do I select, the sum of the first non-zero value from a list of columns- without including the column in the group by. This is the query that I wrote, but I have to include all these columns in the group by (which I dont want to do)

Select CpnyID,H.Acct,A.Descr, 
case when H.YtdBal12 > 0 then sum(H.YtdBal12)
     when H.YtdBal11 > 0 then sum(H.YtdBal06)
     when H.YtdBal10 > 0 then sum(H.YtdBal10)
     when H.YtdBal09 > 0 then sum(H.YtdBal09)
     when H.YtdBal08 > 0 then sum(H.YtdBal08)
     when H.YtdBal07 > 0 then sum(H.YtdBal07)
     when H.YtdBal06 > 0 then sum(H.YtdBal06)
     when H.YtdBal05 > 0 then sum(H.YtdBal05)
     when H.YtdBal04> 0 then sum(H.YtdBal04)
     when H.YtdBal03 > 0 then sum(H.YtdBal03)
     when H.YtdBal02  > 0 then sum(H.YtdBal02)
     when H.YtdBal01 > 0 then sum(H.YtdBal01)
     when H.YtdBal00 > 0 then sum(H.YtdBal00)
END
from AcctHist H,Account A,PSSFAAssetClass C
where A.Acct = H.Acct
and (H.Acct = C.Acct or H.Acct  = C.AccDeprAcct) 
and FiscYr = 2013
group by CpnyID,H.Acct,A.Descr
order by CpnyID,H.Acct

Upvotes: 0

Views: 175

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You want to move the sum() outside the case:

Select CpnyID, H.Acct, A.Descr, 
Sum(case when H.YtdBal12 > 0 then H.YtdBal12
     when H.YtdBal11 > 0 then H.YtdBal06
     when H.YtdBal10 > 0 then H.YtdBal10
     when H.YtdBal09 > 0 then H.YtdBal09
     when H.YtdBal08 > 0 then H.YtdBal08
     when H.YtdBal07 > 0 then H.YtdBal07
     when H.YtdBal06 > 0 then H.YtdBal06
     when H.YtdBal05 > 0 then H.YtdBal05
     when H.YtdBal04> 0 then H.YtdBal04
     when H.YtdBal03 > 0 then H.YtdBal03
     when H.YtdBal02  > 0 then H.YtdBal02
     when H.YtdBal01 > 0 then H.YtdBal01
     when H.YtdBal00 > 0 then H.YtdBal00
END)
from AcctHist H 
join Account A
    On A.Acct = H.Acct
JOIN PSSFAAssetClass C
    on (H.Acct = C.Acct or H.Acct  = C.AccDeprAcct) 
where FiscYr = 2013
group by CpnyID, H.Acct, A.Descr
order by CpnyID, H.Acct;

I also fixed the join syntax.

Upvotes: 2

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

Try this:

SUM(COALESCE(H.YtdBal12, H.YtdBal11, H.YtdBal10...)

You may need a NULLIF() on each column if the values are actually 0 and not just null. Like:

SUM(COALESCE(NULLIF(H.YtdBal12,0),NULLIF(H.YtdBal11,0), etc...)

Upvotes: 2

Related Questions