Запрос MySQL с подсчетом и группой

У меня есть таблица с разными записями для издателей, каждая запись имеет дату в столбце типа 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.

Мне нужно подсчет количества записей, публикуемых каждым издателем за каждый месяц. Например

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

Я пробовал выберите count (id) из группы raw_occurrence_record по месяцам (дата), id_publisher;

но не работает.

1 ответ

Предполагая, что ваша дата является фактическим столбцом datetime:

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

Вы можете соединить свой месяц и год следующим образом:

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

Чтобы найти месяцы, когда нет записей, вам понадобится таблица дат. Если вы не можете создать его, вы можете UNION ALL создать таблицу календаря следующим образом:

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

См. демонстрацию

licensed under cc by-sa 3.0 with attribution.