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

4

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

answered May 23, 2014 at 10:33

G one

2,7392 gold badges17 silver badges18 bronze badges

3 Comments

user2959229 Over a year ago

This appears to work correctly. Going to test to make double sure! Thanks.

G one Over a year ago

Lemme know if there is any issue

user2959229 Over a year ago

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,1666 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

7 Comments

3 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

3 Comments

2 Comments

Linked

Related