How to SUM columns on multiple conditions in a GROUP BY

Question

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 t2.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]

UPDATE

Have corrected the t2 left join syntax as per the comment below.

The output I am expecting is hopefully obvious from the question. For 6 accounts, the SQL should return 6 accounts with their Balance, Income and Outcome of that account.

The problem with the SQL I provided was that the numbers are wrong! As per the comments I think the problem stems from joining multiple times which is summing the amounts incorrectly.

FINAL ANSWER Based on the answers below I have provided the final solution. The problem occurred as I should have only been joining on the transaction table once and then sum with a case statement for the outcome and income values.

select
    a.ACCOUNT_ID,
    a.BANK_NAME,
    a.LOCALE,
    a.[STATUS],
    sum(t.AMOUNT) as BALANCE,
    sum(CASE WHEN t.AMOUNT < 0 THEN t.AMOUNT ELSE 0 end) as OUTCOME,
    sum(CASE WHEN t.AMOUNT > 0 THEN t.AMOUNT ELSE 0 end) as INCOME
from ACCOUNT a
left join [TRANSACTION] t on t.ACCOUNT_ID = a.ACCOUNT_ID
group by a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]

Answer 1

Did you mean:

select 
    a.ACCOUNT_ID, 
    a.BANK_NAME, 
    a.LOCALE, 
    a.STATUS, 
    sum(t1.AMOUNT) as BALANCE, 
    sum(CASE WHEN t2.AMOUNT < 0 THEN t2.Amount ELSE 0 END) as OUTCOME, 
    sum(CASE WHEN t3.AMOUNT > 0 THEN t3.Amount ELSE 0 END) as INCOME 
from ACCOUNT a 
left join TRANSACTION t1 on t1.ACCOUNT_ID = a.ACCOUNT_ID 
left join TRANSACTION t2 on t2.ACCOUNT_ID = a.ACCOUNT_ID
left join TRANSACTION t3 on t3.ACCOUNT_ID = a.ACCOUNT_ID
group by a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS] 

Answer 2

Shouldn't the join for TRANSACTION t2 be on t2 as in on t2.ACCOUNT_ID = a.ACCOUNT_ID ?

Answer 3

Since you didn't tell us what's going wrong (that is, describe the behavior you get in addition to describing the the behavior you expect), it's hard to say where, but there are a couple of possibilities. Neil points out one. Another is that since you join on the transaction table three times, you're pairing transactions with transactions and getting repetitions. Instead, join on the transaction table a single time and change how you sum up the Amount column.

select
    a.ACCOUNT_ID,
    a.BANK_NAME,
    a.LOCALE,
    a.STATUS,
    sum(t.AMOUNT) as BALANCE,
    sum((t.AMOUNT < 0) * t.AMOUNT) as OUTGOING,
    sum((t.AMOUNT > 0) * t.AMOUNT) as INCOMING
from ACCOUNT a
left join TRANSACTION t on t.ACCOUNT_ID = a.ACCOUNT_ID
group by a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]

Using CASE expressions would be more readable than the multiplications.

Answer 4

I'm not sure why you would need the multiple joins. Couldn't you simply do something like:

Select
    a.ACCOUNT_ID
    , a.BANK_NAME
    , a.LOCALE
    , a.STATUS
    , Sum ( t.Amount ) As Balance
    , Sum( Case When t.Amount < 0 Then Amount End ) As Outcome
    , Sum( Case When t.Amount > 0 Then Amount End ) As Income
From ACCOUNT a
    Left Join TRANSACTION t 
        On t.ACCOUNT_ID = a.ACCOUNT_ID
Group By a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]