MySQL Query with count and group by

Question

I've got table a table with different records for publishers, each record have a date in a column of type timestamp.

id | id_publisher | date 1           1      11/2012 03:09:40 p.m. 2           1      12/2012 03:09:40 p.m. 3           2      01/2013 03:09:40 p.m. 4           3      01/2013 03:09:40 p.m. 5           4      11/2012 03:09:40 p.m. 6           4      02/2013 03:09:40 p.m. 7           4      02/2012 03:09:40 p.m. 

I need a count for number of records published by each publisher for each month. For example

Month    |  id_publisher         | num 11/2012  |          1            |   1 11/2012  |          2            |   0 11/2012  |          3            |   0 11/2012  |          4            |   1 ..... 02/2013  |          4            |   2 

I tried with select count(id) from raw_occurrence_record group by month(date), id_publisher;

but, it did not work.

Answer 1

Assuming that your date is an actual datetime column:

SELECT MONTH(date), YEAR(date), id_publisher, COUNT(*) FROM raw_occurrence_record GROUP BY MONTH(date), YEAR(date), id_publisher 

You can concatenate your month & year like so:

SELECT CONCAT(MONTH(date), '/', YEAR(date)) AS Month, id_publisher, COUNT(*) FROM raw_occurrence_record GROUP BY MONTH(date), YEAR(date), id_publisher 

To find months where there are no records, you will need a date table. If you can't create one, you can UNION ALL a calendar table like so:

SELECT a.year, a.month, b.id_publisher, COUNT(b.id_publisher) AS num FROM   (SELECT 11 AS month, 2012 AS year    UNION ALL    SELECT 12, 2012    UNION ALL    SELECT 1, 2013    UNION ALL    SELECT 2, 2013) a LEFT JOIN raw_occurence_record b   ON YEAR(b.date) = a.year AND MONTH(b.date) = a.month GROUP BY a.year, a.month, b.id_publisher 

See a demo