mysql - Selecting count of zero while grouping by column -

i have jobs table, , trying count of jobs different time frames. current query looks this:

select count(*) 'count',         week(j.created_at) 'week',        month(j.created_at) 'month',         year(j.created_at) 'year',         date_format(j.created_at, '%y') 'short_year' jobs j j.state <> 'draft'              , created_at > '2010-06-21'             , created_at < '2010-08-01' group week(j.created_at) order week(j.created_at) 

to change timeframe, change group by week month, , counts month instead of week.

the problem not getting empty rows weeks 0 jobs. result set 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 there no data week 29, should row count(0). there way 0 count row, while maintaining flexibility of changing grouping between week , month?

create auxiliary calendar table containing every date , outer join onto (the below may need tweaking if created_at has time component)

select count(*) 'count',         week(c.date) 'week',        month(c.date) 'month',         year(c.date) 'year',         date_format(c.date, '%y') 'short_year' calendar c left outer join jobs j on j.created_at = c.date             , j.state <> 'draft'  c.date > '2010-06-21'             , c.date < '2010-08-01' group week(c.date) order week(c.date)