MYSql Sub Query with multiple select statement

Question

I've got a table that looks like this

Savings
ID | Name | Type | Amount | Date
1  | Alex | Cash | 100    | 2019-06-10
2  | Nick | CHQ  | 500    | 2019-06-10
3  | Mike | Cash | 700    | 2019-06-10
4  | Luke | CHQ  | 200    | 2019-06-10
5  | Alex | Card | 300    | 2019-06-10
6  | Alex | Card | 100    | 2019-06-10
7  | Luke | Cash | 900    | 2019-06-10
8  | Alex | Cash | 400    | 2019-06-10
9  | Mike | CHQ  | 200    | 2019-06-10

is it possible to sort it out in this manner using only 1 select statement?

Final Output
Name | Total Amount | Total Cash | Total Chq
Mike | 900          | 700        | 200
Luke | 1100         | 900        | 200
Alex | 500          | 500        | 0
Nick | 500          | 0          | 500

This is my current statement

SELECT 
  name, 
  SUM(amount) 
FROM 
  savings 
WHERE 
  date = '2019-06-10' AND 
  type = 'Cash' OR date = '2019-06-10' AND 
  type = 'Chq' 
GROUP BY 
  name 
ORDER BY 
  SUM(amount) DESC

All help appreciated. thanks

Fahmi · Accepted Answer · 2019-06-14 06:01:21Z

5

use conditional aggregation with case when expression

SELECT name, sum(amount) as 'Total Amount',
SUM(case when type = 'Cash' then amount end) as 'Total Cash',
SUM(case when type = 'Chq' then amount end) as 'Total Chq'
FROM savings 
where date = '2019-06-10' group BY name

answered Jun 14, 2019 at 6:01

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

大文茫 · Accepted Answer · 2019-06-14 06:10:11Z

0

For example:

select 
    Name,
    sum(Amount) 'Total Amount',
    sum(if(Type = 'Cash', Amount, 0)) 'Total Cash',
    sum(if(Type = 'CHQ', Amount, 0)) 'Total Chq'
from savings 
group by Name;

answered Jun 14, 2019 at 6:10

大文茫

313 bronze badges

Collectives™ on Stack Overflow

MYSql Sub Query with multiple select statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related