I'm trying to return the count of records within each month and group the result by month / year.
Schema looks something like this:
id title timestamp
I've been searching around but I can't get the result as I expect it. Thanks.
I'm trying to return the count of records within each month and group the result by month / year.
Schema looks something like this:
id title timestamp
I've been searching around but I can't get the result as I expect it. Thanks.
Format the timestamp, then group by it.
Group by Month:
SELECT DATE_FORMAT(t.timestamp, "%Y-%m") AS "_Month", COUNT(*)
FROM yourtable as t
GROUP BY _Month;
Group by Year:
SELECT DATE_FORMAT(t.timestamp, "%Y") AS "_Year", COUNT(*)
FROM yourtable as t
GROUP BY _Year;
If the timestamp-field is stored as a unixtime-value, just wrap FROM_UNIXTIME()
around the field:
SELECT DATE_FORMAT(FROM_UNIXTIME(t.timestamp), "%Y") AS "_Year", COUNT(*)
FROM yourtable as t
GROUP BY _Year;
SELECT YEAR(`timestamp`) AS Y, MONTH(`timestamp`) AS M, COUNT(1) AS C
FROM that_table
GROUP BY YEAR(`timestamp`), MONTH(`timestamp`)
ORDER BY YEAR(`timestamp`), MONTH(`timestamp`)