How to get data back from Mysql for days that have no statistics

Question

Hello all,

I want to get the number of Registrations back from a time period (say a week), which isn't that hard to do, but I was wondering if it is in anyway possible to in MySQL to return a zero for days that have no registrations.

An example: DATA:

ID_Profile    datCreate
1   2009-02-25 16:45:58
2   2009-02-25 16:45:58
3   2009-02-25 16:45:58
4   2009-02-26 10:23:39
5   2009-02-27 15:07:56
6   2009-03-05 11:57:30

SQL:

    SELECT 
        DAY(datCreate) as RegistrationDate, 
    COUNT(ID_Profile) as NumberOfRegistrations 
    FROM tbl_profile
    WHERE DATE(datCreate) > DATE_SUB(CURDATE(),INTERVAL 9 DAY)
    GROUP BY RegistrationDate
    ORDER BY datCreate ASC;

In this case the result would be:

RegistrationDate    NumberOfRegistrations
25  3
26  1
27  1
5   1

Obviously I'm missing a couple of days in between. Currently I'm solving this in my php code, but I was wondering if MySQL has any way to automatically return 0 for the missing days/rows. This would be the desired result:

RegistrationDate    NumberOfRegistrations
25  3
26  1
27  1
28      0
1       0
2       0
3       0
4       0
5   1

This way we can use MySQL to solve any problems concerning the number of days in a month instead of relying on php code to calculate for each month how many days there are, since MySQL has this functionality build in.

Thanks in advance

Answer 1

No, but one workaround would be to create a single-column table with a date primary key, preloaded with dates for each day. You'd have dates from your earliest starting point right through to some far off future.

Now, you can LEFT JOIN your statistical data against it - then you'll get nulls for those days with no data. If you really want a zero rather than null, use IFNULL(colname, 0)

Answer 2

Thanks to Paul Dixon I found the solution. Anyone interested in how I solved this read on:

First create a stored procedure I found somewhere to populate a table with all dates from this year.

CREATE Table calendar(dt date not null);

CREATE PROCEDURE sp_calendar(IN start_date DATE, IN end_date DATE, OUT result_text TEXT)
  BEGIN
    SET @begin = 'INSERT INTO calendar(dt) VALUES ';
    SET @date = start_date;
    SET @max = SUBDATE(end_date, INTERVAL 1 DAY);
    SET @temp = '';
    REPEAT
      SET @temp = concat(@temp, '(''', @date, '''), ');
      SET @date = ADDDATE(@date, INTERVAL 1 DAY);
      UNTIL @date > @max
    END REPEAT;
    SET @temp = concat(@temp, '(''', @date, ''')');
    SET result_text = concat(@begin, @temp);
  END


   call sp_calendar('2009-01-01', '2010-01-01', @z);

   select @z;

Then change the query to add the left join:

SELECT
    DAY(dt) as RegistrationDate,
    COUNT(ID_Profile) as NumberOfRegistrations
FROM calendar
LEFT JOIN 
    tbl_profile ON calendar.dt = tbl_profile.datCreate
WHERE dt  BETWEEN DATE_SUB(CURDATE(),INTERVAL 6 DAY)  AND CURDATE()
GROUP BY RegistrationDate
ORDER BY dt ASC

And we're done.

Thanks all for the quick replies and solution.