mysql group sum same table-CodePudding

I have the following table:

id	name	type	score
1	john	orange	2345
2	john	yellow	5
3	john	black	5454540
4	jack	orange	1123
5	jack	yellow	1000
6	jack	black	86943
7	jane	orange	9876
8	jane	yellow	10000
9	jane	black	102233

comment;

id : inte
name :   same name save more times to more rows, 
score: it is int data
type : it has string data black,white,yellow,purple,orange

I'm using the following queries to calculate two score totals

SELECT name,sum(score) as `first` 
FROM salary 
WHERE type = 'orange' 

SELECT name,sum(score) as `second` 
FROM salary 
WHERE type in ('black','yellow')

i want see result that ( all names must be group, single name.)

name	FirstScore	SecondScore
john	2345	5454545
jack	1123	87943
jane	9876	112233

CodePudding user response：

Use a conditional SUM() to aggregate the values based on type:

 SELECT name
        , SUM(CASE WHEN type IN ('orange') THEN score END ) AS FirstScore
        , SUM(CASE WHEN type IN ('yellow','black') THEN score END ) AS SecondScore
 FROM   salary
 GROUP BY Name

Results:

name	FirstScore	SecondScore
john	2345	5454545
jack	1123	87943
jane	9876	112233

db<>fiddle here

CodePudding user response：

Something like this (but this is untested):

SELECT 
   salary.name,
   first.score as "first_score",
   second.score as "second_score"
FROM salary
LEFT JOIN (SELECT name,sum(score) as score
           FROM salary 
           WHERE type = 'orange'
          ) as first  ON first.name = salary.name
LEFT JOIN (SELECT name,sum(score)  as score
           FROM salary 
           WHERE type in ('black','yellow')
          ) as second ON second.name = salary.name