I've got table a table with different records for publishers, each record have a date in a column of type timestamp.
我有一个表，表中有不同的发布者记录，每个记录在类型为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;
我试着按月份(日期)，id_publisher，从raw_occurrence_record组中选择count(id);
but, dont work.
但是,不工作。
1 个解决方案
#1
29
Assuming that your date is an actual datetime column:
假设您的日期是一个实际的datetime列:
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
#1
29
Assuming that your date is an actual datetime column:
假设您的日期是一个实际的datetime列:
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