How to get a count even if there are no results corresponding mysql?
Solution 1
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
Solution 2
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/
Solution 3
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.
Related videos on Youtube
![tread](https://i.stack.imgur.com/ND705.png?s=256&g=1)
tread
Updated on September 26, 2022Comments
-
tread almost 2 years
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?
-
tread almost 11 yearsHi thanks, This seems legit but I get a FUNCTION reports.month does not exist.Check with function name parsing and resolution.
-
valex almost 11 years@StevieG: try fixed query
-
tread almost 11 yearsIt's working now, but the output is still not outputting months 9 through to 8. So only when a row that meets the conditions it is added. So there are none with a 0 count.
-
tread almost 11 yearsthanks appreciate your time man. Is there a way to make months of previous year ie. 09-2012, 10-2012, appear before the others instead of in ascending order?
-
carinlynchin over 7 yearsI've tried so many ways to do this but I ended up with the error: ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
-
mickmackusa over 3 years"hope this helps" answers do a poor job of educating the OP and thousands of future researchers. Please always explain how your snippet works and why it is a good idea.