Skip to content
Advertisement

SQL query – split out count() results into one line of GROUP BY results

I want to use GROUP BY to get a row per month-year. Additionally, I have another field which, for this example, is colour and can have values of Blue or Red.

I want to see a table with columns for Blue, Red, month and year = I want to count the number of each colour per month-year. Can I do this with an SQL query?

I know that count(colour) will give me the total number of rows for each month-year.

It’s good if it’s possible to do this but some solutions may involve coding in “Red” and “Blue” = just 2 values. Is it possible to run a query to execute a count which will split out the “answers” from the count into each line rather than a line each?

Example data:

Year Month Day Colour
2021 3 6 Blue
2021 3 7 Blue
2021 3 8 Blue
2021 3 9 Red
2021 4 5 Blue
2021 4 6 Red
2021 4 7 Blue
2021 4 8 Red
2021 4 9 Red

to give result

Year Month Blue Red
2021 3 3 1
2021 4 2 3

I’m doing this in mysql and also in javascript using alasql but a suggestion for any version of SQL will probably be helpful here…

Advertisement

Answer

You can use SUM(CASE WHEN... to do this

SELECT Year, Month, SUM(CASE WHEN Colour = 'Blue' THEN 1 ELSE 0 END) AS Blue, etc
FROM table
GROUP BY Year, Month
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement