Ketan Patel    06-Apr-2020

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_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.

 

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 click here to see the answer.

 

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.

About Ketan Patel

A Web Developer having 7+ years of experience in web development. Worked with Laravel-Lumen, CakePHP, CodeIgniter, Opencart to make sites for startup companies and small businesses. Apart from blogging, he likes to read Novels, Listening music and Net surfing.