'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

This is the table: (I enlightened two rows to point the matter

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

The enlightened row should have two different scores

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