'Cells are merged in a Query which asks different sums
I have a database of cars with the Columns "Plate", "Type" (0 for cars, 1 for trucks, and so on), "Horsepower", "Color", "Emission Standard" (Euro 0, Euro 1, and so on, but the field is just INT), "Travelled distance".
I want to get a table that groups all the vehicles by "Type", "Horsepower", "Color" and the sum of the travelled distance of Euro 0 category of that type/horsepower/Color, the sums for euro 1 category, the sum for euro 2 category etc.
EDIT: as suggested, I'm reporting the exact table I have, and the exact results
The query is
SELECT type, horsepower, Color, if(emission_category = 0, round(sum(travelled_distance),3), null) as EURO0, if(emission_category = 1, round(sum(travelled_distance),3), null) as EURO1, if(emission_category = 2, round(sum(travelled_distance),3), null) as EURO2, if(emission_category = 3, round(sum(travelled_distance),3), null) as EURO3, if(emission_category = 4, round(sum(travelled_distance),3), null) as EURO4, if(emission_category = 5, round(sum(travelled_distance),3), null) as EURO5, if(emission_category = 6, round(sum(travelled_distance),3), null) as EURO6 FROM database_verde.veichles GROUP BY type, horsepower, Color
Instead of getting two different scores for the columns EURO 3 and 4, the two rows enlightened in the previous picture are merged in a single column
What am I doing wrong? How do I set the condition to sum ONLY if the row fits all the requirements?
Solution 1:[1]
I managed out by myself. Thank anyway. Here's what i did
SELECT type, horsepower, Color, sum(CASE WHEN emission_category = 0 THEN travelled_distance ELSE null END) as EURO0,
sum(CASE WHEN emission_category = 1 THEN travelled_distance ELSE null END) as EURO1,
sum(CASE WHEN emission_category = 2 THEN travelled_distance ELSE null END) as EURO2,
sum(CASE WHEN emission_category = 3 THEN travelled_distance ELSE null END) as EURO3,
sum(CASE WHEN emission_category = 4 THEN travelled_distance ELSE null END) as EURO4,
sum(CASE WHEN emission_category = 5 THEN travelled_distance ELSE null END) as EURO5,
sum(CASE WHEN emission_category = 6 THEN travelled_distance ELSE null END) as EURO6
FROM database_verde.veichles
GROUP BY type, horsepower, Color
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Vincenzo Costantini |