Aggregating data by date in a date range without date gaps in result set

Question

I have a table with sell orders and I want to list the COUNT of sell orders per day, between two dates, without leaving date gaps.

This is what I have currently:

SELECT COUNT(*) as Norders, DATE_FORMAT(date, "%M %e") as sdate 
FROM ORDERS 
WHERE date <= NOW() 
  AND date >= NOW() - INTERVAL 1 MONTH 
GROUP BY DAY(date) 
ORDER BY date ASC;

The result I'm getting is as follows:

6     May 1
14    May 4
1     May 5
8     Jun 2
5     Jun 15

But what I'd like to get is:

6     May 1
0     May 2
0     May 3
14    May 4
1     May 5
0     May 6
0     May 7
0     May 8
.....
0     Jun 1
8     Jun 2
.....
5     Jun 15

Is that possible?

@GordonLinoff No, I have only the ORDERs table, which contains only rows for the days that an order was created. — alexandernst, Jul 02 '14 at 14:17
@oliakaoil Yes, exactly. That is the point. The `ORDERS` table doesn't contain rows for each day, but just for the days where orders were created. — alexandernst, Jul 02 '14 at 14:22

Kickstart · Accepted Answer · 2014-07-02T15:45:59.253

4

Creating a range of dates on the fly and joining that against you orders table:-

SELECT sub1.sdate, COUNT(ORDERS.id) as Norders
FROM
(
    SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY), "%M %e") as sdate 
    FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units
    CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens
    CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)hundreds
    WHERE DATE_SUB(NOW(), INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()
) sub1
LEFT OUTER JOIN ORDERS
ON sub1.sdate = DATE_FORMAT(ORDERS.date, "%M %e")
GROUP BY sub1.sdate

This copes with date ranges of up to 1000 days.

Note that it could be made more efficient easily depending on the type of field you are using for your dates.

EDIT - as requested, to get the count of orders per month:-

SELECT aMonth, COUNT(ORDERS.id) as Norders
FROM
(
    SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL months.i MONTH), "%Y%m") as sdate, DATE_FORMAT(DATE_SUB(NOW(), INTERVAL months.i MONTH), "%M") as aMonth 
    FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11)months
    WHERE DATE_SUB(NOW(), INTERVAL months.i MONTH) BETWEEN DATE_SUB(NOW(), INTERVAL 12 MONTH) AND NOW()
) sub1
LEFT OUTER JOIN ORDERS
ON sub1.sdate = DATE_FORMAT(ORDERS.date, "%Y%m")
GROUP BY aMonth

edited Jul 02 '14 at 15:45

answered Jul 02 '14 at 14:27

Kickstart

21,403
2
21
33

Nice trick :) Just one bug, it returns `1` instead of `0` the days I don't have any sell orders. – alexandernst Jul 02 '14 at 14:32
Shouldn't do, but it does require the COUNT(...) to be counting a column in the orders table (I assumed id) which will have a value of NULL (count and a column name will ignore null values) when there are no orders. If you use COUNT(*) it will count a row with no orders as 1. – Kickstart Jul 02 '14 at 14:33
Ah, indeed. You're right. I just wrote * instead of `id`. Thank you, that works perfectly! – alexandernst Jul 02 '14 at 14:34
Just one more question on the same topic. How should I modify the `sub1` subselect if I wanted to show the `COUNT` of orders per month, for the last 12 months? – alexandernst Jul 02 '14 at 14:48
Added a suggestion for that. – Kickstart Jul 02 '14 at 14:52
There is a missing comma (`,`) right after `as sdate` and before `DATE_FORMAT...` (in the subselect). Also, I'm getting an error, "Unknown column 'units.i in field list". – alexandernst Jul 02 '14 at 14:57
I'm not able to find where does that error comes from (the "Unknown units" error). And I also spoted another bug: the result isn't ordered by date, but by natural order (1, 10, 11, 2, 22...) – alexandernst Jul 02 '14 at 15:07
Think I have just fixed that now. – Kickstart Jul 02 '14 at 15:47
Sorry for not replying earlier, I wasn't at work. Yes, now it works almost as I expect. The only missing thing is the order. Your query orders by natural order (April, August, Decembre, February...), while I'd expect it to order by dates (January, February, March, April...). – alexandernst Jul 03 '14 at 07:28
You can add `ORDER BY dDate` to change the sort order (sDate includes the year so will sort December to January correctly). – Kickstart Jul 03 '14 at 08:08
I tried sorting by `sDate`, but it keeps sorting in natural order. Maybe it's because by the time the sort is ran, `sDate` is already a string instead of a date? – alexandernst Jul 03 '14 at 08:16
Shouldn't matter. It is formatted as (say) 201402 for February 2014 (unless a typo has sneaked in and you have changed it to `DATE_FORMAT(DATE_SUB(NOW(), INTERVAL months.i MONTH), "%Y%M") as sdate` ). Add sDate to the outer queries select and double check what it contains. – Kickstart Jul 03 '14 at 08:19
Ah, I see what you mean :) Yes, I guess that would work. It's still sorting by natural order, but we can trick it so it would match what I want. Thank you! – alexandernst Jul 03 '14 at 08:30

score 3 · Answer 2 · answered Jul 02 '14 at 14:41

You are going to need to generate a virtual (or physical) table, containing every date in the range.

That can be done as follows, using a sequence table.

SELECT mintime + INTERVAL seq.seq DAY AS orderdate
  FROM (
        SELECT CURDATE() - INTERVAL 1 MONTH AS mintime,
               CURDATE() AS maxtime
          FROM obs
       ) AS minmax
  JOIN seq_0_to_999999 AS seq ON seq.seq < TIMESTAMPDIFF(DAY,mintime,maxtime)

Then, you join this virtual table to your query, as follows.

SELECT IFNULL(orders.Norders,0) AS Norders, /* show zero instead of null*/
       DATE_FORMAT(alldates.orderdate, "%M %e") as sdate 
  FROM (
        SELECT mintime + INTERVAL seq.seq DAY AS orderdate
          FROM (
                SELECT CURDATE() - INTERVAL 1 MONTH AS mintime,
                       CURDATE() AS maxtime
                  FROM obs
               ) AS minmax
          JOIN seq_0_to_999999 AS seq 
                        ON seq.seq < TIMESTAMPDIFF(DAY,mintime,maxtime)
       ) AS alldates
  LEFT JOIN (
    SELECT COUNT(*) as Norders, DATE(date) AS orderdate
      FROM ORDERS 
    WHERE date <= NOW() 
      AND date >= NOW() - INTERVAL 1 MONTH 
    GROUP BY DAY(date) 
       ) AS orders ON alldates.orderdate = orders.orderdate
ORDER BY alldates.orderdate ASC

Notice that you need the LEFT JOIN so the rows in your output result set will be preserved even if there's no data in your ORDERS table.

Where do you get this sequence table seq_0_to_999999? You can make it like this.

DROP TABLE IF EXISTS seq_0_to_9;
CREATE TABLE seq_0_to_9 AS
   SELECT 0 AS seq UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9;

DROP VIEW IF EXISTS seq_0_to_999;
CREATE VIEW seq_0_to_999 AS (
SELECT (a.seq + 10 * (b.seq + 10 * c.seq)) AS seq
  FROM seq_0_to_9 a
  JOIN seq_0_to_9 b
  JOIN seq_0_to_9 c
);

DROP VIEW IF EXISTS seq_0_to_999999;
CREATE VIEW seq_0_to_999999 AS (
SELECT (a.seq + (1000 * b.seq)) AS seq
  FROM seq_0_to_999 a
  JOIN seq_0_to_999 b
);

You can find an explanation of all this in more detail at http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/

If you're using MariaDB version 10+, these sequence tables are built in.

+1 for pointing out MariaDB 10+ has built in sequence tables. — Quaternion, Jul 02 '14 at 16:48

score 1 · Answer 3 · edited May 23 '17 at 11:51

1

First create a Calendar Table

SELECT coalesce(COUNT(O.*),0) as Norders, DATE_FORMAT(C.date, "%M %e") as sdate 
FROM Calendar C 
  LEFT JOIN ORDERS O ON C.date=O.date
WHERE O.date <= NOW() AND O.date >= NOW() - INTERVAL 1 MONTH 
GROUP BY DAY(date) 
ORDER BY date ASC;

edited May 23 '17 at 11:51

Community

1
1

answered Jul 02 '14 at 14:25

Horaciux

6,322
2
22
41

That would require having a `Calendar` table that has rows for each day on each month of each year, right? – alexandernst Jul 02 '14 at 14:27
@alexandernst Yes, follow the link posted in answer http://stackoverflow.com/questions/5635594/how-to-create-a-calender-table-for-100-years-in-sql – Horaciux Jul 02 '14 at 14:27
Not quite efficient, IMHO. I'd leave that as a plan B if nothing else works. – alexandernst Jul 02 '14 at 14:33
@alexandernst I'd recommend you do some research on tally/numbers/sequence tables uses. Regards – Horaciux Jul 02 '14 at 14:47

Aggregating data by date in a date range without date gaps in result set

3 Answers3

Linked

Related