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

[Rick James]

JOIN to a simple table that contains weekNum values (or simply numbers).
Let's say you create table Numbers and populate it with 1, 2, ... 1000.
Then something like this will do the trick:

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

Have fun in January.