How to SUM columns on multiple conditions in a GROUP BY
- by David Liddle
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]
