Using SQL, how to calculate count of rows for each ID(column) for each month by using only datetime and put them in monthly columns?

Question

I am relatively new to SQL. I have a dataset as follows:

   'ID'            'date'
     1      2016-01-01 01:01:06
     2      2016-01-02 02:02:07
     1      2016-01-03 03:03:08
     3      2016-04-04 04:04:09
     2      2016-04-05 05:05:00

I want to obtain smth like this:

   'ID'       'Count: Jan'     'Count: Feb'    'Count: March'    'Count: April'
     1              2               0                 0                 0
     2              1               0                 1                 0
     3              0               0                 0                 1

I really have no idea how handle this. I could put the data creating a column "month" and another column "count" but I want to be able to have a table like this.

Thanks in advance

Does this answer your question? [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — WOUNDEDStevenJones, Nov 20 '20 at 21:15

score 0 · Accepted Answer · answered Nov 20 '20 at 21:13

You ca use conditional aggregation:

select id,
       sum(case when month(date) = 1 then 1 else 0 end) as cnt_jan,
       sum(case when month(date) = 2 then 1 else 0 end) as cnt_feb,
       . . .
       sum(case when month(date) = 12 then 1 else 0 end) as cnt_dec
from t
group by id;

Using SQL, how to calculate count of rows for each ID(column) for each month by using only datetime and put them in monthly columns?

1 Answers1