sql - How to get a count even if there are no results corresponding mysql?

Question

I am formalating a query to give the number of reports submitted over the last year ordered by date. I get the current year and month with php:

$year = date('Y') - 1;
$month = date('m');

and execute the following query: SQL:

SELECT month(date_lm) AS `month` ,
count(*) AS `count`
FROM `reports` 
WHERE (status = 'submitted') 
AND (date_lm > 2012-08) 
GROUP BY month(date_lm) 
ORDER BY month(date_lm) ASC

And because there has only been 1 submitted in the last year it gives me only 1 result...

| month  |  count  |
|   7    |    1    |

But I would like the result set to show:

| month  |  count  |
|   9    |    0    |
|   10   |    0    |
|   11   |    0    |
|   12   |    0    |
|   1    |    0    |
|   2    |    0    |
|   3    |    0    |
|   4    |    0    |
|   5    |    0    |
|   6    |    0    |
|   7    |    1    |
|   8    |    0    |

Is that possible?

Answer 1

In order to do this, you could create a 'month' table and then use a left outer join between that table and the reports table.

I've never used mysql so apologies if the syntax is slightly off, but this would be the query:

SELECT months.monthNumber,
    count(reports.id) AS `count`
FROM `months` left outer join `reports` on months.monthNumber = month(reports.date_lm) 
WHERE (status = 'submitted') 
AND (date_lm > 2012-08) 
GROUP BY monthNumber
ORDER BY monthNumber ASC

Importantly, the count should be of a column in the reports table, not the months table, or else you would never get a zero.