SQL select statement help; conditional sum 2

[stroyer74]

I'm running this SQL statement

select sum(AMOUNT), AMOUNT_TYPE, GL_ACCOUNT_ID
from RECEIVABLE_DIST
group by GL_ACCOUNT_ID, AMOUNT_TYPE


which returns

AMOUNT TYPE GL_ACCOUNT_ID
500.00 CR 0110-000
100.00 DR 0110-000


which means that my total for account 0110-000 should be 400.00. Instead of manually making that calculation, is there a way to change my SQL statement to use a conditional summing? If the amounts type is CR, sum it as a positive number, if type is a DR, sum it as a negative.


Any ideas would be greatly appreciated.

Sheldon

 

[rsshetty]

select
case AMOUNT_TYPE
when 'CR' then sum(AMOUNT)
else 0-sum(AMOUNT)
end as Sum_Amount, AMOUNT_TYPE, GL_ACCOUNT_ID
from RECEIVABLE_DIST
group by GL_ACCOUNT_ID, AMOUNT_TYPE

Not sure if this will work. You can give it a shot.

rsshetty.
It's always in the details.

 

[manjarikosaraju]

select sum(case when amtType = 'CR' then Amt when amtType = 'DR' then -1*Amt end), ActID
from #Amts
group by ActID

 

[stroyer74]

rsshetty,

Thanks for the fast response!

That gets me half way there. That statement returns:

Sum_Amount TYPE GL_ACCOUNT_ID
500.00 CR 0110-000
-100.00 DR 0110-000



I don't think I completely clarified in my post what my final result should be. What I am hoping to end up with is:

Sum_Amount GL_ACCOUNT_ID
400.00 0110-000


Thanks,
Sheldon

 

[stroyer74]

Thanks manjarikosaraju! That is exactly what I was looking for. I am new to the case statement - I'm sure I will use that a lot in the future.

Sheldon