How to SUM columns on multiple conditions in a GROUP BY
Posted
by David Liddle
on Stack Overflow
See other posts from Stack Overflow
or by David Liddle
Published on 2010-05-02T11:09:50Z
Indexed on
2010/05/02
11:17 UTC
Read the original article
Hit count: 186
I am trying to return a list of Accounts with their Balances, Outcome and Income
Account Transaction
------- -----------
AccountID TransactionID
BankName AccountID
Locale Amount
Status
Here is what I currently have. Could someone explain where I am going wrong?
select
a.ACCOUNT_ID,
a.BANK_NAME,
a.LOCALE,
a.STATUS,
sum(t1.AMOUNT) as BALANCE,
sum(t2.AMOUNT) as OUTCOME,
sum(t3.AMOUNT) as INCOME
from ACCOUNT a
left join TRANSACTION t1 on t1.ACCOUNT_ID = a.ACCOUNT_ID
left join TRANSACTION t2 on t1.ACCOUNT_ID = a.ACCOUNT_ID and t2.AMOUNT < 0
left join TRANSACTION t3 on t3.ACCOUNT_ID = a.ACCOUNT_ID and t3.AMOUNT > 0
group by a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]
© Stack Overflow or respective owner