Group by week, how to get empty weeks?

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 w开发者_开发知识库eeks, 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

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.

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;

This is the sort of problem for which numbers/tally tables are made - just a simple table that counts from 0 to whatever. They're useful in general, I'd always recommend adding them to your database.

With this you can relatively easily generate a list of weeks on the fly without needing a specific precalculated table of weeks (so you don't need either a huge table or to worry about running out of weeks), then left join that against your list of appointments to show all weeks with an appointment in them.

For a quick start:

declare @min    datetime
set     @min    ='2010-01-01'

select
    dateadd(wk, number, @min) as weekDate
from
    numbers
where
    number between 0 and datediff(wk,@min,getdate())

with