MySQL WEEK() : Get all weeks in date range (with/without records)

Question

Here's an sql query I am using to get count of records in a table separated by week (only date is stored in table). It works as expected.

SELECT count(id), CONCAT('Week ',WEEK(complaintRaisedDate)) week
FROM events
WHERE categoryId=1
GROUP BY week
ORDER BY week

This yields result like

count(id)       week
---------- | ----------

1               Week 36
2               Week 40
1               Week 41

How I want the result to be is below:

count(id)       week
---------- | ----------
1               Week 36
0               Week 37
0               Week 38
0               Week 39
2               Week 40
1               Week 41

That is, if no records found for a particular week it should still show the week (within the date range of records in table) with a count of 0. I can figure out a way to do this in PHP, but I was wondering if it can be achieved with a little tweaking of the SQL query itself. Is it possible? Thanks.

Edit: SQLFiddle

Guillermo

@Mihai sqlfiddle added. – Guillermo

Answer 1

Assuming you have a table of integers (called `numbers` below):

   SELECT COALESCE(n, 0) AS num_complaints, CONCAT('Week ', i) AS `week`
     FROM (SELECT i
             FROM numbers
            WHERE i BETWEEN (SELECT WEEK(MIN(complaintRaisedDate)) FROM events LIMIT 1)
                            AND
                            (SELECT WEEK(MAX(complaintRaisedDate)) FROM events LIMIT 1))
          week_ranges
LEFT JOIN (  SELECT count(id) AS n, WEEK(complaintRaisedDate) AS weeknum
               FROM events
              WHERE categoryId=1
           GROUP BY weeknum) weekly_tallies
       ON week_ranges.i = weekly_tallies.weeknum
 ORDER BY `week` ASC;

SQL fiddle

Refrangible

Answer 2

Try: http://sqlfiddle.com/#!2/5dfbf/36

CREATE  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 count(events.id), ifnull(CONCAT('Week ',WEEK(complaintRaisedDate)),0) week
FROM events RIGHT OUTER JOIN weeks ON WEEK(events.complaintRaisedDate) = weeks.id
GROUP BY weeks.id 
HAVING weeks.id>=(SELECT MIN(WEEK(events.complaintRaisedDate)) FROM events)
AND weeks.id<=(SELECT MAX(WEEK(events.complaintRaisedDate)) FROM events);

Latitudinarian

Just a half assed attempt for now.Something like this? – Latitudinarian

Not sure if I understand the solution clearly. It pulls up data for all 53 weeks which I don;t need. Suppose range of date in the table is from Aug 1 to Aug 31st of 2013. So it is 4 weeks. Now I want results to be shown only for these 4 weeks, even if count is 0 in any of these week, but no other week before and after this range. Also I want the week number to return not as null, (think of it like a range/interval if I may) but only the count to be 0 if there's no record for that week. – Guillermo