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;