How to Calculate User Balance Using Credit and Debit in MySQL

To calculate a user's balance using credit and debit in MySQL, you can use a simple SQL query that subtracts the total of all debits from the total of all credits for that user. The resulting balance can then be stored in a separate table or displayed in real-time as needed.

We have two table having values as given below:

id name
1 Gil
2 Kevin
3 Michael
4 Ethan


id user_id type amount
1 1 DEB 1000
2 2 CRE 500
3 3 CRE 200
4 4 DEB 1000
5 1 CRE 5000
6 3 DEB 100
7 4 CRE 3000



  • Get total_credittotal_debit and total_available balance of all the users with their names.
  • CRE: credit, DEB: debit
  • Result should be as given in below table.


name total_credit total_debit total_available
Gil 5000 1000 4000
Kevin 500 0 500
Michael 200 100 100
Ethan 3000 1000 2000


Try at your end and see if you can get the similar result using your query. If you don't know the query then see the answer given below:


    sum(COALESCE( case when type = 'CRE' then amount END, 0)) as total_credit,
    sum(COALESCE( case when type = 'DEB' then amount END, 0)) as total_debit,
    sum(COALESCE( case when type = 'CRE' then amount END, 0)) - sum(COALESCE( case when type = 'DEB' then amount END, 0)) as total_available 
from users_trans as ut
    join users as u 
    on = ut.user_id
group by ut.user_id


If you have the better solution then kindly share with me on with post title as subject line, I will publish it here.

If you remember any MySQL query related quetion that you have been asked in any interview then kindly share it with me in .txt file. So we can create a collection of MySQL query related interview questions that can help others to clear interview.

Ketan Patel

As a backend and ecommerce developer, I have extensive experience in implementing robust and scalable solutions for ecommerce websites and applications. I have a deep understanding of server-side technologies and have worked with various programming languages. I have experience in integrating with payment gateways, and other third-party services.