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