Group by week, how to get empty weeks?

Question

I have the following code, which groups some events (YYYY-MM-DD HH:MM:SS) in weeks which are displayed as for example "Y:2010 - Week: 50".

SELECT DATE_FORMAT(date, 'Y:%X - Week:%V') AS regweek, COUNT(*) as number 

it works good, but I would like to return all weeks, even if the ones in which no event is placed.

If no event is registered in the third week, at the moment I get:

week 1: 10
week 2: 1
week 4: 2

I would like to get:

week 1: 10
week 2: 1
week 3: 0
week 4: 2

Answer 1

SQL cannot return rows that don't exist in some table. To get the effect you want, you will need a table Weeks (WeekNo INT) with one row per possible week of the year (which, IIRC, is either 53 or 54 possible weeks, depending on how you count).

Then, JOIN this table to your regular results with an OUTER JOIN to get the extra weeks added in.

SELECT DATE_FORMAT(date, 'Y:%X - Week:%V') AS regweek, COUNT(date) as number 
    FROM YourTable RIGHT OUTER JOIN Weeks ON WEEK(YourTable.date) = Weeks.WeekNo

[Update]: Note the user of COUNT(date) rather than COUNT(*). SQL will not include NULL values in the date column when adding up the COUNT. Since the missing weeks will not have any dates in them, this will correctly give you 0 events for those weeks.

Answer 2

At the end I decided to solve the issue as follow, creating a temporary table for the weeks and using the code that was found out in the answer here above.

CREATE TEMPORARY TABLE weeks (
         id INT
       );
INSERT INTO weeks (id) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40), (41), (42), (43), (44), (45), (46), (47), (48), (49), (50), (51), (52), (53), (54);
SELECT 
w.id, COUNT(c.issuedate) as numberPerWeek
FROM tableName c RIGHT OUTER JOIN weeks w ON WEEK(c.issuedate) = w.id group by w.id;