MySQL Forums
Forum List  »  Newbie

Forcing COUNT() to return value even when no records exist
Posted by: Trevor Lynn
Date: August 25, 2009 01:25AM

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

Options: ReplyQuote


Subject
Written By
Posted
Forcing COUNT() to return value even when no records exist
August 25, 2009 01:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.