CODEWITHSUNDEEP
mysql
Fernando
Fernando

Reputation: 4629

Group by day and still show days without rows?

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.

Upvotes: 5

Views: 3586

Answers (4)

Evgeny Bychkov
Evgeny Bychkov

Reputation: 332

DECLARE @TOTALCount INT
DECLARE @FromDate DateTime = GetDate() - 5
DECLARE @ToDate DateTime = GetDate()

SET @FromDate = DATEADD(DAY,-1,@FromDate)
Select  @TOTALCount= DATEDIFF(DD,@FromDate,@ToDate);

WITH d AS 
        (
          SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER() 
            OVER (ORDER BY object_id), REPLACE(@FromDate,'-',''))
          FROM sys.all_objects
        )
SELECT AllDays From d

Upvotes: 0

Justin Johnson
Justin Johnson

Reputation: 31300

I'm not sure if this is a problem that should be solved by SQL. As others have shown, this requires maintaining a second table that contains the all of the individual dates of a given time span, which must be updated every time that time span grows (which presumably is "always" if that time span is the current time.

Instead, you should use to inspect the results of the query and inject dates as necessary. It's completely dynamic and requires no intermediate table. Since you specified no language, here's 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 that

Upvotes: 0

Anonym
Anonym

Reputation: 7725

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)

Upvotes: 7

gahooa
gahooa

Reputation: 137332

In order to accomplish this, you need to have a table (or derived table) which contains the dates that you can then join from, using a LEFT JOIN.

SQL operates on the concept of mathematical sets, and if you don't have a set of data, there is nothing to SELECT.

If you want more details, please comment accordingly.

Upvotes: 6

Related Questions