How to get a count even if there are no results corresponding mysql?
You should LEFT JOIN this table with 1..12 table. Something like this:
SELECT Months.id AS `month` ,
COUNT(`reports`.date_lm) AS `count`
FROM
(
SELECT 1 as ID UNION SELECT 2 as ID UNION SELECT 3 as ID UNION SELECT 4 as ID
UNION
SELECT 5 as ID UNION SELECT 6 as ID UNION SELECT 7 as ID UNION SELECT 8 as ID
UNION
SELECT 9 as ID UNION SELECT 10 as ID UNION SELECT 11 as ID UNION SELECT 12 as ID
) as Months
LEFT JOIN `reports` on Months.id=month(`reports`.date_lm)
AND
(status = 'submitted')
AND (date_lm > 2012-08)
GROUP BY Months.id
ORDER BY Months.id ASC
SQL Fiddle demo
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.
count(col_name) AS count
will give you count 0
For reference you can visit http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/