Mysql: Count records (including zero) per month

Question

Hi,

I am trying to count the records in my table and group them per date. My current query looks something like the following:

SELECT
   count(*), 
   MONTH(time) as month,
   YEAR(time) as year
FROM
   myTable
GROUP BY
   month, year
ORDER BY
   year, month

This works, except that I would also like to get a count for months where no records exists.

Could anyone offer advice/suggestions on how to accomplish this?

Answer 1

The simplest way to do this in MySQL is to create a table called months that lists all the months you are interested in and use a LEFT JOIN to your table.

SELECT
   YEAR(time) AS year
   MONTH(time) AS month,
   COUNT(myTable.year) AS cnt, 
FROM months
LEFT JOIN myTable 
    ON months.year = myTable.year
    AND months.month = myTable.month
GROUP BY months.year, months.month
ORDER BY months.year, months.month

However since this is mostly a presentation issue it is often easier just run the query as you are already doing and transform the result in the client (e.g. PHP).