Skip to content
Advertisement

mysql group 2 rows into 1 with only one different value between the rows

I currently have this query:

SELECT Club_Name, SUM(Data_km) AS km, Type_id
FROM Data
INNER JOIN Clubs ON Data.Data_Club = Clubs.Club_id
INNER JOIN Types ON Clubs.Club_Type = Types.Type_id
WHERE Club_isActive = 1
GROUP BY Club_Name, Type_id

which gives me this table:

club_name km type
A 10 1
A 15 2
B 80 1
B 34 2
C 36 1

And I’m trying to achieve:

club_name type 1 km type 2 km
A 10 15
B 80 34
C 36

not all clubs will have a type 2. How do I rewrite the query to achieve the reorganization?

Advertisement

Answer

You can use conditional aggregation:

SELECT Club_Name, 
       SUM(CASE WHEN Type_id = 1 THEN Data_km END) as km_1,
       SUM(CASE WHEN Type_id = 2 THEN Data_km END) as km_2
FROM Data d JOIN
     Clubs c
     ON d.Data_Club = c.Club_id JOIN
     Types t
     ON c.Club_Type = t.Type_id
WHERE Club_isActive = 1
GROUP BY Club_Name;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement