I have a table like t
his table:
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 was solve single query.
SELECT year(date) as year, date as month, (SELECT COUNT(level_id)) as L1 FROM attendance WHERE level_id = 'L1' AND status = 'P' AND year(date) = '2016' GROUP BY date
I want a table like this table :
The following query should give you the result you want. Just use conditional aggregation along with a GROUP BY to count the number of times each type of level appears for a given year and month.
SELECT YEAR(date) AS year,
MONTH(date) AS month,
SUM(CASE WHEN level_id = 'L1' THEN 1 ELSE 0 END) AS L1,
SUM(CASE WHEN level_id = 'L2' THEN 1 ELSE 0 END) AS L2,
SUM(CASE WHEN level_id = 'L3' THEN 1 ELSE 0 END) AS L3
FROM yourTable
WHERE status = 'P' AND
YEAR(date) = 2016
GROUP BY YEAR(date),
MONTH(date)
As a tip for future reference, if you wanted to avoid ordering by two separate columns, you could have also used this:
GROUP BY DATE_FORMAT(date, '%Y-%m')
This would give you a single column containing the year and month, and might make your query a bit less verbose.
WHERE clause.