2

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
ceejayoz
  • 176,543
  • 40
  • 303
  • 368
  • Figured there wasn't an obvious way of doing this, but figured I'd ask. Thanks to all who responded. – ceejayoz Dec 30 '08 at 17:15

6 Answers6

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
John Nilsson
  • 17,001
  • 8
  • 32
  • 42
  • I knew there had to be a way to do this without a calendar or numbers table. And this is easily adapted to other intervals (I needed grouping every 15 minutes). – Corin Jan 22 '14 at 14:56
3

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.

LeppyR64
  • 5,251
  • 2
  • 30
  • 35
  • It's a bit of a maintenance pain, although it's more performant than to create a SP that dynamically generates the list. – Vinko Vrsalovic Dec 30 '08 at 16:37
  • The maintenance issue here really means that creating a calendar table isn't appropriate. You'd end up with a calendar table that's the centre of the whole database, and debugging becomes very difficult. – Neil Barnwell Dec 30 '08 at 16:42
  • @Neil, I must say that I agree, but that I don't like creating the calendar table on the fly, unless of course it is to be done periodically, as opposed to often. – LeppyR64 Dec 30 '08 at 17:01
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.

ChrisW
  • 54,973
  • 13
  • 116
  • 224
  • While a valid method, Thangalin's answer below programmatically does this without having to create any new tables. – glasnt Jun 22 '09 at 23:19
  • Thangallin said, "In Oracle", but the OP tagged the question "mysql". – ChrisW Jun 23 '09 at 13:48
0

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
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
0

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.

Chuck
  • 8,282
  • 2
  • 29
  • 24
-1

There's a pretty straightforward way to do this… except that I can't remember it. But I adapted this query from this thread:

SELECT 
    DISTINCT(LEFT(date_field,11)) AS `Date`,
    COUNT(LEFT(date_field,11)) AS `Number of events`
FROM events_table
GROUP BY `Date`

It works in MySQL too

fregante
  • 29,050
  • 14
  • 119
  • 159