SQL group by date, but get dates w/o records too

Question

Is there an easy way to do a GROUP BY DATE(timestamp) that includes all days in a period of time, regardless of whether there are any records associated with that date?

Basically, I need to generate a report like this:

24 Dec - 0 orders
23 Dec - 10 orders
22 Dec - 8 orders
21 Dec - 2 orders
20 Dec - 0 orders

Answer 1

One method is to create a calendar table and join against it.

I would create it permanently, and then create a task that will insert new dates, it could be done weekly, daily, monthly, etc.

Note, that I am assuming that you are converting your timestamp into a date.

Answer 2

Instead of using GROUP BY, make a table (perhaps a temporary table) which contains the specific dates you want, for example:

24 Dec
23 Dec
22 Dec
21 Dec
20 Dec

Then, join that table to the Orders table.

Answer 3

you need to generate an intermediate result set with all the dates in it that you want included in the output...

if you're doing this in a stored proc, then you could create a temp table or table variable (I don't knoiw MySQL's capabilities), but once you have all the dates in a table or resultset of some kind

Just join to the real dataa from the temp table, using an outer join

In SQL Server it would be like this

  Declare @Dates Table (aDate DateTime Not Null)
  Declare @StartDt DateTime Set @StartDt = 'Dec 1 2008'
  Declare @EndDt   DateTime Set @EndDt   = 'Dec 31 2008'
  While @StartDt < @EndDt Begin
    Insert @Dates(aDate) Values(@StartDt)
    Set @StartDt = DateAdd(Day, 1, @StartDt)
  End

   Select D.aDate, Count(O.*) Orders
   From @Dates D Left Join 
      OrderTable O On O.OrderDate = D.aDate
   Group By D.aDate

Answer 4

In a data warehouse, the method taken is to create a table that contains all dates and create a foreign key between your data and the date table. I'm not saying that this is the best way to go in your case, just that it is the best practice in cases where large amounts of data need to be rolled up in numerous ways for reporting purposes.

If you are using a reporting layer over SQL Server, you could just write some logic to insert the missing dates within the range of interest after the data returns and before rendering your report.

If you are creating your reports directly from SQL Server and you do not already have a data warehouse and there isn't the time or need to create one right now, I would create a date table and join to it. The formatting necessary to do the join and get the output you want may be a bit wonky, but it will get the job done.

Answer 5

Assuming you have more orders than dates something like this could work:

select date, count(id) as orders
from
(
  SELECT DATE_ADD('2008-01-01', INTERVAL @rn:=@rn+1 DAY) as date from (select @rn:=-1)t, `order` limit 365
) d left outer join `order` using (date)
group by date

Answer 6

No Table Required

In Oracle, you do not need to create a temporary table. You can use CONNECT BY LEVEL to generate the date range. For example:

SELECT
  to_char( LEVEL + to_date( '01 DEC', 'DD MON' ) - 1, 'DD MON' ) as created_date
FROM
  dual
CONNECT BY LEVEL <= 31

Examples

List of days between two arbitrary dates, to a maximum of 31 days.

  SELECT
      to_date( &start_date, 'DD-MM-YYYY' ) + LEVEL - 1 match_date
  FROM
      DUAL
  CONNECT BY LEVEL <= (to_date( &end_date,'DD-MM-YYYY' ) - to_date( &start_date,'DD-MM-YYYY' ) + 1) AND LEVEL <= 31

List of months between two arbitrary dates, to a maximum of 12 months.

  SELECT
      to_char( add_months( to_date( &start_date, 'DD-MM-YYYY' ), LEVEL - 1 ), 'MM-YYYY' ) match_date
  FROM
      DUAL
  CONNECT BY LEVEL <= months_between( to_date( &end_date, 'DD-MM-YYYY' ), to_date( &start_date, 'DD-MM-YYYY' ) ) + 1 AND LEVEL <= 12

List of years between two arbitrary dates, to a maximum of 10 years.

  SELECT
      to_char( add_months( to_date( &start_date, 'DD-MM-YYYY' ), (LEVEL - 1) * 12 ), 'YYYY' ) match_date
  FROM
      DUAL
  CONNECT BY LEVEL <= months_between( to_date( &end_date, 'DD-MM-YYYY' ), to_date( &start_date, 'DD-MM-YYYY' ) ) / 12 + 1 AND LEVEL <= 10