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:

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.

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.