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

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...

CodePudding user response：

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

CodePudding user response：

I will suggest fixing the design. You could store date in one column only, in date format. Be aware of the MySQL reserved word such as YEAR, MONTH,DATE, you should use backticks for those columns.

If you only have blue and red color you could do an easy solution:

SELECT Year, 
       Month, 
       SUM(Colour='Blue') as Blue, 
       SUM(Colour='Red') as Red   
FROM test_tbl    
GROUP BY `Year`, `Month`;

Result:

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

Demo

CodePudding user response：

You could do the following:

  GROUP BY Year(yourdate),
     Month(yourdate),
     Day(yourdate),
     Colour

Group By is not limited to acting on a single column (or expression).

CodePudding user response：

You can write a query like this

SELECT Year, 
       Month, 
       COUNT(CASE WHEN Colour = 'Blue' THEN 1 END) AS Blue, 
       COUNT(CASE WHEN Colour = 'Red' THEN 1 END) AS Red    
FROM table    
GROUP BY Year, Month;