Mysql: count records (including zero) per month

Question

I am trying to count the entries in my table and group them by date. My current query looks something like this:

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 receive an invoice for several months where there are no entries.

Can anyone offer tips / suggestions on how to do this?

Answer 1

The easiest way to do this in MySQL is to create a table with a name monthsthat lists all the months of interest to you and use LEFT JOIN for 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

, , , , (, PHP).