Selecting count of zero while grouping by column

Question

I have a jobs table, and am trying to get a count of jobs for different time frames. My current query looks like this:

SELECT COUNT(*) AS 'count', 
       WEEK(j.created_at) AS 'week',
       MONTH(j.created_at) AS 'month', 
       YEAR(j.created_at) AS 'year', 
       DATE_FORMAT(j.created_at, '%y') AS 'short_year'
FROM jobs j WHERE j.state <> 'draft' 
            AND created_at > '2010-06-21'
            AND created_at < '2010-08-01'
GROUP BY WEEK(j.created_at)
ORDER BY WEEK(j.created_at)

To change my timeframe, I simply change the GROUP BY from WEEK to MONTH, and I get counts by month instead of week.

The problem is that I am not getting empty rows for weeks with 0 jobs. My result set from the query above is:

count week  month  year short_year
    3   25      6  2010         10
    2   26      6  2010         10
    2   27      7  2010         10 
    1   28      7  2010         10
    3   30      7  2010         10

You'll notice that there is no data for week 29, which should be a row with count(0). Is there any way to get that 0 count row, while maintaining the flexibility of changing my grouping between WEEK and MONTH?

Answer 1

Create an auxiliary calendar table containing every date and outer join onto that (the below may need tweaking if created_at has a time component)

SELECT COUNT(*) AS 'count', 
       WEEK(c.date) AS 'week',
       MONTH(c.date) AS 'month', 
       YEAR(c.date) AS 'year', 
       DATE_FORMAT(c.date, '%y') AS 'short_year'
FROM calendar c
LEFT OUTER JOIN jobs j ON j.created_at = c.date
            AND j.state <> 'draft' 
WHERE c.date > '2010-06-21'
            AND c.date < '2010-08-01'
GROUP BY WEEK(c.date)
ORDER BY WEEK(c.date)