Returning empty rows in GROUP BY clause

Question

I have a query to retrieve a total number of events per day between two dates.

SELECT
  DATE_FORMAT(startTime, '%Y%m%d') as day,
  COUNT(0) as numEvents
FROM events
WHERE
  (startTime BETWEEN 20140105000000 AND 20140112235900)
GROUP BY
  day;

This return a list like

day      | numEvents
---------+----------
20140105 | 45
20140107 | 79
20140108 | 12
20140109 | 56

Notice that there are missing days in the result since there are no events in the events table for those days. Is there any way to return 0 values for those days, resulting in:

day      | numEvents
---------+----------
20140105 | 45
20140106 | 0
20140107 | 79
20140108 | 12
20140109 | 56
20140110 | 0
20140111 | 0
20140112 | 0

Answer 1

There are quite a few solutions on SO:

• How do I include empty rows in a single GROUP BY DAY(date_field) SQL query?
• generate empty rows even if empty between 2 dates
• SQL Group By including empty rows
• MySQL GROUP BY and Fill Empty Rows
• Summarise by week, even for empty rows

One solution is to you another table that contains all possible/relevant dates. One implementation can be found here: https://stackoverflow.com/a/7262803/1085891

This is typically done in data warehouses by having a date dimension that has a list of all possible dates. You do an OUTER JOIN to the date dimension and COALESCE the null values to 0.

If run often, it would be more efficient to store the dates. However, for a quick report you could generate the dates you need and join the output to the table you are searching against.

• t-sql get all dates between 2 dates
• SQL Server: How to select all days in a date range even if no data exists for some days
• Get all dates in date range in SQL Server

Answer 2

Try this

SELECT  DATE_FORMAT(startTime, '%Y%m%d') as day, COUNT(DATE_FORMAT(startTime, '%Y%m%d'))
FROM  events 
WHERE
(startTime BETWEEN 20140105000000 AND 20140112235900)
GROUP BY day;

Answer 3

You will able to do it if startTime is a foreign_key to another table which have values for every date. This is tipical on multidimensional models of data-warehouses, where you have a date/time dimension and them include all days.

In that case, I think the solution provided by Vigens Kumar would work:

SELECT  DATE_FORMAT(startTime, '%Y%m%d') as day, COUNT(DATE_FORMAT(startTime, '%Y%m%d'))
FROM  events 
WHERE
(startTime BETWEEN 20140105000000 AND 20140112235900)
GROUP BY day;