Group by day and still show days without rows?

Question

I have a log table with a date field called logTime. I need to show the number of rows within a date range and the number of records per day. The issue is that i still want to show days that do not have records.

Is it possible to do this only with SQL?

Example:

SELECT logTime, COUNT(*) FROM logs WHERE logTime >= '2011-02-01' AND logTime <= '2011-02-04' GROUP BY DATE(logTime);

It returns something like this:

+---------------------+----------+
| logTime             | COUNT(*) |
+---------------------+----------+
| 2011-02-01          |        2 |
| 2011-02-02          |        1 |
| 2011-02-04          |        5 |
+---------------------+----------+
3 rows in set (0,00 sec)

I would like to show the day 2011-02-03 too.

Answer 1

MySQL will not invent rows for you, so if the data is not there, they will naturally not be shown.

You can create a calendar table, and join in that,

create table calendar (
    day date primary key,
);

Fill this table with dates (easy with a stored procedure, or just some general scripting), up till around 2038 and something else will likely break unitl that becomes a problem.

Your query then becomes e.g.

SELECT logTime, COUNT(*) 
  FROM calendar cal left join logs l on cal.day = l.logTime 
WHERE day >= '2011-02-01' AND day <= '2011-02-04' GROUP BY day;

Now, you could extend the calendar table with other columns that tells you the month,year, week etc. so you can easily produce statistics for other time units. (and purists might argue the calendar table would have an id integer primary key that the logs table references instead of a date)