Menu
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.
438
The use of COUNT() function in conjunction with GROUP BY is useful for characterizing our data under various groupings. A combination of same values (on a column) will be treated as an individual group.
We can use GROUP BY to group together rows that have the same value in the Animal column, while using COUNT() to find out how many ID's we have in each group. It returns a table with three rows (one for each distinct animal).
To count the number of rows, use the id column which stores unique values (in our example we use COUNT(id) ). Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause.
MySQL COUNT() Function The COUNT() function returns the number of records returned by a select query.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
