Selecting and counting all orders of the past 30 days

Question

I'm currently trying to get all orders of the past 30 days and return the total and how many orders there were. If a day has no orders I still want a row in my result (obviously with count and total == 0).

Is there a way to do this?

There are a lot of questions asking this without the requirement of always getting 30 rows. However, I can't comment on them because my reputation is too low.

For example: selecting all orders for last 30 days, and counting how many per day

Do you really need to do this? Can't the code processing the results just know it's a zero if it isn't in the SQL? — Tim B, Mar 24 '17 at 12:30
If you really want to do all of this in the SQL, this sounds like a job for a stored procedure to me. The sproc would create a temp table where you insert 30 records (1 for each day), then come back and update order totals for each of those days. That would get you your desired output of always having 30 days in your result set — mituw16, Mar 24 '17 at 12:31
I have an order table which contains an ID, order time, and the price (and some customer data, but I don't think this is relevant). — Dennis Hein, Mar 24 '17 at 12:35
I'd prefer to do it with an SQL query. I could add the missing arrays in PHP before passing it to the template, but that sounds like more work than it should be... — Dennis Hein, Mar 24 '17 at 12:37

score 3 · Accepted Answer · edited May 23 '17 at 11:47

Days generator is taken from here generate days from date range

another job is simple left join to grouped orders

(change with your table and column names in left join subquery)

select a.Date, pr.orders_count, pr.price
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
left join (select order_time, count(*) as orders_count, sum(price) as price from orders group by order_time) pr
on a.Date = pr.order_time
where  a.Date >= NOW() - INTERVAL 30 DAY 
order by a.Date desc

A word of warning about using NOW() here. NOW() returns the current time AND date, for example 2017-03-24 1330. NOW() - INTERVAL 30 DAY would return 2017-02-22 1330. If your Order Dates include a Time component, wittingly or unwittingly, then any Order from that Date with an earlier Time would be excluded. If you wish to avoid NOW()'s Time component, try using CURDATE() instead. If there is a Time component in your Order Dates, try using DATE(). e.g. WHERE DATE( a.Date ) >= CURDATE() - INTERVAL 30 DAY — toonice, Mar 24 '17 at 16:06
Good catch! Will update the code to use `CURDATE()` after the weekend. — Dennis Hein, Mar 24 '17 at 20:20

score 1 · Answer 2 · answered Mar 24 '17 at 12:48

1

You could use a calendar table or date dimension for this.

select td.db_date, count(o.OrderId) as Orders
from time_dimension td
  left join Orders o
    on date(o.OrderDate) = db_date
where td.db_date >= date(adddate(curdate(), interval -30 day))
  and td.db_date <= date(curdate())
group by td.db_date

MySQl Calendar table reference

answered Mar 24 '17 at 12:48

SqlZim

37,248
6
41
59

1

Very interesting read! I like how readable the query becomes when you've got that calendar table. However, I kind of dislike that one has to create an entire new table for this (seemingly simple) problem. – Dennis Hein Mar 24 '17 at 20:22
@DennisHein I'm glad you found it interesting! I understand your hesitance concerning creating a table to facilitate queries. I wish I had a good reference that bench-marked the performance of queries using various sized date ranges and compared different approaches, e.g. Calendar table vs stacked values (accepted answer). The accepted answer is very fast, especially for a 30 day window, I'm sure any difference at that range wouldn't be noticeable. – SqlZim Mar 24 '17 at 20:41

score 0 · Answer 3 · edited May 23 '17 at 10:30

0

You can use generator for dates from Get a list of dates between two dates

After you can use outter join with your order table.

edited May 23 '17 at 10:30

Community

1
1

answered Mar 24 '17 at 12:39

Dmitry T.

101
2

toonice · Answer 4 · 2017-03-24T16:19:25.313

0

Try -

SELECT dateDuringPeriod, SUM( orderValue ), COUNT( orderID )
FROM
(
    SELECT dateDuringPeriod,
           IFNULL( orderID, 0 ) orderID,
           IFNULL( orderValue, 0 ) orderValue
    FROM
    (
        SELECT DATE_ADD( CURDATE(), INTERVAL singles + tens DAY) dateDuringPeriod
        FROM 
        (
            SELECT 0 singles
            UNION ALL SELECT -1
            UNION ALL SELECT -2
            UNION ALL SELECT -3
            UNION ALL SELECT -4
            UNION ALL SELECT -5
            UNION ALL SELECT -6
            UNION ALL SELECT -7
            UNION ALL SELECT -8
            UNION ALL SELECT -9
        ) singles JOIN 
        (
            SELECT 0 tens
            UNION ALL SELECT -10
            UNION ALL SELECT -20
        ) tens
    ) datesDuringPeriod LEFT JOIN 
    (
        SELECT orderID,
               DATE( orderDate ) orderDate,
               orderValue
        FROM tblOrders
    ) ordersWithoutTimes
ON datesDuringPeriod.dateDuringPeriod = ordersWithoutTimes.orderDate
) tblOrderSummary
GROUP BY dateDuringPeriod
ORDER BY dateDuringPeriod;

I have created a sample table with sample data and found this code to be effective.

edited Mar 24 '17 at 16:19

answered Mar 24 '17 at 12:56

toonice

2,211
1
13
20

Original answer updated to MySQL syntax (sorry - programming with a cold and feeling woozier than I thought). Please feel free to ask any questions you might have. – toonice Mar 24 '17 at 13:46
Hey, thanks for taking the time. Your answer sadly doesn't completely work. If there is a day without orders the corresponding row will be missing. – Dennis Hein Mar 24 '17 at 14:30
Oops! My bad. I'll get stuck into the problem now. – toonice Mar 24 '17 at 14:40
I appreciate your effort, but want to let you know that Oto Shavadze has posted a working solution. Just in case you want to save some time :) – Dennis Hein Mar 24 '17 at 15:05
Actually, I'm mainly interested in using this as a stimulating programming exercise and in helping out. It's great if I get the points, but I'm mainly interested in any feedback that I get. – toonice Mar 24 '17 at 15:45
Answer updated. It now produces the full list. Once again, I would appreciate any comments offered. – toonice Mar 24 '17 at 15:49
Answer updated to be more in line with my comment on Oto Shavadze's answer. Also, layout slightly improved. – toonice Mar 24 '17 at 16:21

Selecting and counting all orders of the past 30 days

4 Answers4