shalder shalder - 11 days ago 5
MySQL Question

How to count rows for three levels(L1, L2, L3) column and status P for every month in a year

I have a table like this table:

enter image description here

So I want to count the rows for three levels (L1,L2,L3) as column and status is

P
for every month in a year.

I want a table like this table :

enter image description here

Answer

SELECT id, year(date) as year, month(date) as month ,if( level_id = 'L1',count(level_id), 0) as L1 ,if( level_id = 'L2',count(level_id), 0) as L2 FROM stak GROUP BY date