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: 191

Filed under:
|

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

Related posts about sql

Related posts about tsql