mysql count 返回0_MySql count（）返回0，如果找不到记录

I have a set of posts on monthly basis. Now i need an array which contains total records of posts posted in each month. I tried below MySql query, Its working fine, but I was expecting 0(Zero) for months where there is no records. Here its not returning 0.

I read that COUNT() will not return '0', So how do i achieve this?

I tried IFNULL(), and COALESCE() but still getting the same result. Please help with this query. Thank You......

SELECT

count(id) as totalRec

FROM ('post')

WHERE year(date) = '2013'

AND monthname(date) IN ('January', 'February', 'March')

GROUP BY year(date)-month(date)

ORDER BY 'date' ASC

Got Result:

+----------+

| totalRec |

+----------+

| 7 |

| 9 |

+----------+

Expected Result (Where there is no posts for January):

+----------+

| totalRec |

+----------+

| 0 |

| 7 |

| 9 |

+----------+

Sample Data:

+----+---------------------+

| id | date |

+----+---------------------+

| 24 | 2012-12-16 16:29:56 |

| 1 | 2013-02-25 14:57:09 |

| 2 | 2013-02-25 14:59:37 |

| 4 | 2013-02-25 15:12:44 |

| 5 | 2013-02-25 15:14:18 |

| 7 | 2013-02-26 11:31:31 |

| 8 | 2013-02-26 11:31:59 |

| 10 | 2013-02-26 11:34:47 |

| 14 | 2013-03-04 04:39:02 |

| 15 | 2013-03-04 05:44:44 |

| 16 | 2013-03-04 05:48:29 |

| 19 | 2013-03-07 15:22:34 |

| 20 | 2013-03-15 12:24:43 |

| 21 | 2013-03-16 16:27:43 |

| 22 | 2013-03-16 16:29:28 |

| 23 | 2013-03-16 16:29:56 |

| 11 | 2013-03-17 11:35:12 |

+----+---------------------+

解决方案

There is no record for the month of January that is why you are getting no result. One solution that works is by joining a subquery with contains list of months that you want to be shown on the list.

SELECT count(b.id) as totalRec

FROM (

SELECT 'January' mnth

UNION ALL

SELECT 'February' mnth

UNION ALL

SELECT 'March' mnth

) a

LEFT JOIN post b

ON a.mnth = DATE_FORMAT(b.date, '%M') AND

year(b.date) = '2013' AND

DATE_FORMAT(b.date, '%M') IN ('January', 'February', 'March')

GROUP BY year(b.date)-month(b.date)

ORDER BY b.date ASC

OUTPUT

╔══════════╗

║ TOTALREC ║

╠══════════╣

║ 0 ║

║ 7 ║

║ 9 ║

╚══════════╝