Forcing COUNT() to return value even when no records exist

[Trevor Lynn]

Hi there,

If anyone can suggest how to do this I'd be grateful:

I have a bookings table that holds bookings by date. I want to produce a 8 week history in my application that shows number of bookings per week for the last 8 weeks, using week number in the year. This seems easy enough as something like:

SELECT COUNT(bookingID) AS number, WEEK(date) AS weekNum
FROM bookings
WHERE WEEK(date) <= WEEK(DATE_ADD(CURDATE(), INTERVAL -1 WEEK))
AND WEEK(date) >= WEEK(DATE_ADD(CURDATE(), INTERVAL -9 WEEK))
GROUP BY weekNum

This produces output showing number of bookings by week number like:

count(bookingID) week(date)
1 34
3 35

I'd like to see the results for the last 8 weeks regardless of whether there were bookings or not, like:

count(bookingID) week(date)
0 28
0 29
0 30
0 31
0 32
0 33
1 34
3 35

Any suggestions? Thanks.

Trevor