user2959229 user2959229 - 6 months ago 102
MySQL Question

Using MySQL to calculate balances from debits and credits in a single table

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.

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


My MySQL query that doesn't work:

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;


The result I am expecting is something like:

CLIENT_ID DEBITS CREDITS BALANCE
1 1000 600 400
2 1000 1200 -200
4 1000 0 1000


I have no idea what else to try. Any help would be great.

Answer
DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(transaction_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,client_id INT NOT NULL
,action_type VARCHAR(12) NOT NULL
,action_amount INT NOT NULL
);

INSERT INTO my_table(client_id,action_type,action_amount) VALUES
(1            ,'debit',         1000),
(1            ,'credit',         100),
(1            ,'credit',         500),
(2            ,'debit',          1000),
(2            ,'credit',         1200),
(3            ,'debit',          1000),
(3            ,'credit',         1000),
(4            ,'debit',          1000);


SELECT client_id
     , SUM(COALESCE(CASE WHEN action_type = 'debit' THEN action_amount END,0)) total_debits
     , SUM(COALESCE(CASE WHEN action_type = 'credit' THEN action_amount END,0)) total_credits
     , SUM(COALESCE(CASE WHEN action_type = 'debit' THEN action_amount END,0)) 
     - SUM(COALESCE(CASE WHEN action_type = 'credit' THEN action_amount END,0)) balance 
  FROM my_table 
 GROUP  
    BY client_id
HAVING balance <> 0;


+-----------+--------------+---------------+---------+
| client_id | total_debits | total_credits | balance |
+-----------+--------------+---------------+---------+
|         1 |         1000 |           600 |     400 |
|         2 |         1000 |          1200 |    -200 |
|         4 |         1000 |             0 |    1000 |
+-----------+--------------+---------------+---------+
Comments