1

I have table : "orders"(order_id,order_processed_date). I want count of orders per day for last 30 days. If any date has 0 orders, then it should print 0.

Something like this:

total  | date
1      | 2018-10-20
0      | 2018-10-19
0      | 2018-10-18
0      | 2018-10-17
2      | 2018-10-16
0      | 2018-10-15
1      | 2018-10-14
0      | 2018-10-13
0      | 2018-10-12
1      | 2018-10-11
1      | 2018-10-10
5      | 2018-10-09
1      | 2018-10-08

and so on upto 2018-09-20. I already searched in stackoverflow and get some queries but did not find exact solution for this. I get result using below query but it has only records which date has not 0 orders:

SELECT COUNT(order_id) AS total, DATE(order_processed_date) AS date
FROM orders 
WHERE order_processed_date BETWEEN '2018-09-20' AND '2018-10-20'
GROUP BY DATE(order_processed_date)
ORDER BY order_processed_date DESC

Can please someone help me to give me result as I required.

Ketan Lathiya
  • 732
  • 2
  • 8
  • 23
  • Possible duplicate of [MySQL how to fill missing dates in range?](https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) – Nick Oct 20 '18 at 12:55

1 Answers1

0

You could try to use a calendar table, an example of which is described here. Basically, this is a table with an ordered list of dates (+ extra properties if you'd like).

You could use the calendar table to LEFT JOIN your orders on, and use a GROUP BY to get the results e.g. per month.

Best of luck.

Melle
  • 7,639
  • 1
  • 30
  • 31