SQLize Online / PHPize Online

A A A

Table: transactions

id	client_id	transaction_type	amount
1	1	credit	1000
2	1	debit	100
3	1	credit	500
4	1	debit	300
5	2	debit	1000
6	2	credit	1200
7	3	debit	1000
8	3	credit	1000
9	4	debit	1000

Question: How we can get each client balance after each transaction?

Solution:

Tags: PostgreSQL MySQL 8.x SQL Server 2017/2019

In PostgreSQL, Microsoft SQL Server and MySQL since version 8.0 we can WINDOW FUNCTIONS select transaction_id, client_id, case when action_type='credit' then action_amount else 0 end credit, case when action_type='debit' then action_amount else 0 end debit, sum(case when action_type='credit' then action_amount else -action_amount end) over(partition by client_id order by transaction_id) balance from transactions order by client_id, transaction_id;

Use next links for test the above solution: PostgreSQL 12 fiddle MySQL 8.0 fiddle MS SQL 2017 fiddle

Tags: MySQL 5.x

Legacy MySQL server have not window function implemented, so the solution is possible using variables: SELECT transaction_id, client_id, IF(action_type = "credit", action_amount, 0) as CrAmt, IF(action_type <> "credit", action_amount, 0) as DrAmt, @csum := @csum + IF(action_type = "credit", action_amount, -action_amount) as balance FROM transactions WHERE client_id = '1' ORDER BY transaction_id;

Use this link for MySQL 5.7 fiddle code test

---

If you find an error on this page, please contact us via feedback form