0

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?

1
  • 1
    its a lot easy to handle this in your application. detect the gap, and then fill the results. – danblack Sep 29 '18 at 2:16
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.