How mysql count birth month each class and columns are classes

Question

The data in mysql db like this and show a result in Datagrid in WPF

 ---------------------------
 class student_ID birthday
 A     1          2000/1/1
 A     5          2001/4/1
 B     2          2000/1/12
 C     3          2001/8/5
 .     .          .
 .     .          .
 Z     1000       2000/12/2
---------------------------

I know use group by and with rollup can select subtotal and total

but I have no idea to do like statistic table

----------------------------------
 month\class    A    B    C ... Z
 JAN            1    2    3 ... 0
 FEB            0    5    6 ... 7
 .              .    .    .
 .              .    .    .
 DEC            4    8    6 ... 1
----------------------------------

One of my way is using case each month,but if the problem becomes statistic date not month, 365 columns is impossible.

What is google way to solve the problem?

https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns — Andy, Dec 18 '18 at 21:34

score 0 · Answer 1 · answered Dec 18 '18 at 18:32

0

What about:

select
  monthname(birthday),
  sum(case when class = 'A' then 1 end) as a,
  sum(case when class = 'B' then 1 end) as b,
  -- add the other 24 letters here
from my_table
group by monthname(birthday)

answered Dec 18 '18 at 18:32

The Impaler

45,731
9
39
76

but if the class is not fix, should i use loop to do it? – Raccoon Dec 19 '18 at 00:57
Then you need a real table "pivot". I think MySQL doesn't support those. – The Impaler Dec 19 '18 at 14:43

How mysql count birth month each class and columns are classes

1 Answers1