MySQL – Querying a Date Range Effectively

MySQL

I have an orders table. Imagine it having only 2 columns, id and created_at.

Imagine I have a data in my orders table that looks something like this:

id | created_at
01 | 2018-09-29
02 | 2018-09-29
03 | 2018-09-30

I want to select all orders within that day.

I can select the records you by using the following query.

SELECT created_at AS date, COUNT(id) AS result 
FROM orders
GROUP BY created_at
ORDER BY date

This will return:

date | result
2018-09-29 | 2
2018-09-30 | 1

I want to expand the query above to generate the following result instead: (daterange)

date | result
2018-09-15 | 0
2018-09-16 | 0
...
2018-09-29 | 2
2018-09-30 | 1

Is this possible? To select a data that is non-existent to generate a date range report?

Best Answer

You wanted the result must be like this:

 date       | result
2018-09-01   |    0
2018-09-02   |    0
2018-09-03   |    0
2018-09-04   |    0
....
2018-09-28   |    0
2018-09-29   |    2
2018-09-28   |    1

You need to create a table or temporary table for number of days

id 
1
2
3
4
...
28
29
30
31

And try to query this: 

SELECT 
    DATE((select CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()),'-' ,day))) as Date1, 
        COUNT(o.order_id) 
        as counting from orders as o 
        right join numberofdays d 
        on d.day = DAY(o.created_at) 
        GROUP BY Date1
        ORDER BY Date1

I used RIGHT JOIN:

See image

Hope this might help. Thanks.

Related Question