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

Question

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.

AND balance!=0 doesn't work. You have to use having SUM(t_debits)-SUM(t_credits)<>0 — Jens
– Jens, Commented May 23, 2014 at 10:31
GROUP BY t_debits.client_id, t_credits.client_id; is not nesseary if t_debits.client_id = t_credits.client_id. You only need GROUP BY t_debits.client_id — Jens
– Jens, Commented May 23, 2014 at 10:32

Strawberry · Accepted Answer · 2014-05-23 10:37:26Z

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 | +-----------+--------------+---------------+---------+

+1 . . . I prefer this method because it uses a having clause instead of a subquery.
Me too - although I like the "ELSE 0" part of the other method.
. . This is your answer. You can add in the else 0 and remove the coalesce().
After taking a closer look I have decided to go for this one as the answer, with the modification that @GordonLinoff suggested. I prefer doing this without using a subquery.
@user2959229 . . . In other databases, it wouldn't make a difference but MySQL materializes subqueries (yuck!). Even in this case, it makes little difference, because the group by has to read and write the data multiple times. However, when using MySQL, I just think it is better form to avoid subqueries except when necessary.

G one · Accepted Answer · 2014-05-23 10:33:52Z

You need to use case statement

select client_id, debits, credits, debits-credits as balance from (SELECT client_id, SUM(case when ACTION_TYPE='debit' then action_amount else 0 end) AS debits, SUM(case when ACTION_TYPE='credit' then action_amount else 0 end) AS credits FROM categories GROUP BY client_id) a where debits-credits<>0;

Fiddle

This appears to work correctly. Going to test to make double sure! Thanks.
This one did the job, but I've gone with the other answer that does not use a subquery.

Josua M C · Accepted Answer · 2017-01-25 07:06:59Z

1

USE THIS SMALLEST QUERY

 select CLIENT_ID, sum(if(ACTION_TYPE='credit',ACTION_AMOUNT,0)) as credit, sum(if(ACTION_TYPE='debit',ACTION_AMOUNT,0)) as debit, sum(if(ACTION_TYPE='credit',ACTION_AMOUNT,-1*ACTION_AMOUNT)) as balance FROM YOUR_TABLE GROUP BY CLIENT_ID

edited Jan 25, 2017 at 7:06

Josua M C

3,1566 gold badges47 silver badges89 bronze badges

answered May 21, 2016 at 22:34

Sandesh Mhatre

1293 bronze badges

2 Comments

felix-eku Over a year ago

If you could explain how your query works, it would make this a real answer.

Josua M C Over a year ago

This is not working SQL error: ERROR: function if(boolean, double precision, double precision) does not exist

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

3 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

2 Comments

Linked

Related