Group by day and still show days without lines?

Question

I have a log table with a date field called logTime. I need to show the number of rows in a date range and the number of records per day. The problem is that I still want to show the days when they have no entries .

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

To do this, you need to have a table (or view) that contains dates with which you can join using LEFT JOIN.

SQL is working on the concept of mathematical sets, and if you don't have a dataset, SELECT has nothing to do.

If you would like more information, please comment on this.

Answer 2

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

You can create a calendar table and join it,

 create table calendar ( day date primary key, ); 

Fill this table with dates (easily using a stored procedure or just some common scripts), until 2038, and something else is likely to break unitl, which will become a problem.

Then your request becomes, for example,

 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 can expand the calendar table to other columns that indicate the month, year, week, etc., so that you can easily create statistics for other units of time. (and purists might argue that the calendar table has an integer primary key identifier that refers to a table of log tables instead of a date)

Answer 3

I am not sure if this is a problem that should be solved by SQL. As others have shown, this requires maintaining a second table containing all the individual dates of a given time interval, which should be updated each time the time interval is increased (which is supposedly "always" if this time interval is the current time.

Instead, you should use to check query results and enter dates as needed. It is fully dynamic and does not require an intermediate table. Since you did not specify a language, here is the pseudo code:

 EXECUTE QUERY `SELECT logTime, COUNT(*) FROM logs WHERE logTime >= '2011-02-01' AND logTime <= '2011-02-04' GROUP BY DATE(logTime);` FOREACH row IN query result WHILE (date in next row) - (date in this row) > 1 day THEN CREATE new row with date = `date in this row + 1 day`, count = `0` INSERT new row IN query result AFTER this row ADVANCE LOOP INDEX TO new row (`this row` is now the `new row`) END WHILE END FOREACH 

Or something like this