Choosing the number of zeros when grouping by column

Question

I have a task table, and I'm trying to get the number of tasks for different time frames. My current request 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 just change GROUP BYfrom WEEKto MONTHand get a monthly count instead of a week.

The problem is that I do not get blank lines for weeks with 0 jobs. My result obtained from the query:

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 will notice that there is no data for week 29, which should be the line with the score (0). Is there a way to get this string 0 count, while maintaining the flexibility of changing my grouping between WEEKand MONTH?

Answer 1

, ( , created_at )

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)