MySQL GROUP BY date - how to return results when no rows

Question

I am writing a query to return the number of blog posts written per day over a certain period of time. My problem arises when there are no records of a blog for a given day. With my query, the result for that day is simply skipped altogether.

Here's my query:

SELECT DATE(`posted`), COUNT(`id`) 
    FROM `blogs` WHERE `status` = 'active' 
    && `posted` BETWEEN `2011-01-01` AND `2011-05-01` 
    GROUP BY DATE(`posted`)

It returns something similar to:

count | date
_________________
2     |  2011-01-01
5     |  2011-01-02
1     |  2011-01-04

Notice that it is missing 2011-01-03 because it doesn't have any posts.

How do I get it to show those days with 0 posts?

Answer 1

You'd need to have a table that contains all the dates you're going to query over, and do something along the lines of...

SELECT DATE(D.`thedate`), COUNT(`id`)
FROM `datetable` D
LEFT JOIN `blogs` B
ON B.`posted` = D.`thedate`
WHERE `status` = 'active'     
&& D.`thedate` BETWEEN `2011-01-01` AND `2011-05-01`     
GROUP BY DATE(D.`thedate`)