title: How to SUM columns on multiple conditions in a GROUP BY
body: <p>I am trying to return a list of Accounts with their Balances, Outcome and Income</p>
  
  <pre><code>Account            Transaction
  -------            -----------
  AccountID          TransactionID
  BankName           AccountID
  Locale             Amount
  Status
  </code></pre>
  
  <p>Here is what I currently have. Could someone explain where I am going wrong?</p>
  
  <pre><code>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 &lt; 0
  left join TRANSACTION t3 on t3.ACCOUNT_ID = a.ACCOUNT_ID and t3.AMOUNT &gt; 0
  group by a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]
  </code></pre>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Have corrected the t2 left join syntax as per the comment below.</p>
  
  <p>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.</p>
  
  <p>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.</p>