title: Using MySQL to calculate balances from debits and credits in a single table
body: <p>With the following MySQL table containing debit or credit "actions" with associated amounts, how is it possible to select all CLIENT_IDs with a non-zero "balance"? I have tried joining the table to itself in order to calculate all debit and credit totals, but something isn't working correctly.</p>
  
  <pre><code>CLIENT_ID    ACTION_TYPE    ACTION_AMOUNT
  1            debit          1000
  1            credit         100
  1            credit         500
  2            debit          1000
  2            credit         1200
  3            debit          1000
  3            credit         1000
  4            debit          1000
  </code></pre>
  
  <p>My MySQL query that doesn't work:</p>
  
  <pre><code>SELECT 
      client_id,
      SUM(t_debits) AS debits, 
      SUM(t_credits) AS credits, 
      SUM(t_debits)-SUM(t_credits) AS balance
  FROM table_name AS t_debits
  LEFT JOIN table_name AS t_credits ON t_credits.client_id=t_debits.client_id
  WHERE 
      t_debits.action_type='debit'
      AND t_credits.action_type='credit'
      AND balance!=0
  GROUP BY t_debits.client_id, t_credits.client_id;
  </code></pre>
  
  <p>The result I am expecting is something like:</p>
  
  <pre><code>CLIENT_ID    DEBITS    CREDITS    BALANCE
  1            1000      600        400
  2            1000      1200       -200
  4            1000      0          1000
  </code></pre>
  
  <p>I have no idea what else to try. Any help would be great.</p>