MySQL, Tips • 1 year ago

How to Calculate User Balance Using Credit and Debit in MySQL

Learn how to calculate user balance using credit and debit in MySQL. Our tutorial provides step-by-step instructions to help you manage your finances.

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:

users
id name
1 Gil
2 Kevin
3 Michael
4 Ethan

 

users_transactions
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

 

Task:

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

 

Result
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:

 

select 
    u.name, 
    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 u.id = ut.user_id
group by ut.user_id

 

If you have the better solution then kindly share with me on ketan32.patel@gmail.com 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.

Categories

Tags