one mysql query for all count of orders on each day

Question

I am trying to create generic query for generating various reports on day selection.

For example,

for last 7 days order value, I need following output.

Order Date Order Value

2014-05-15 - 700
2014-05-16 - 0
2014-05-17 - 100
2014-05-18 - 0
2014-05-19 - 0
2014-05-20 - 0
2014-05-21 - 200

if order data is not presented for the date then 0 should be displayed on that date.

You can use "SELECT order_date, COALESCE(NULLIF(order_value, ""), "0") as order_value' — Afshan Shujat, Apr 30 '16 at 14:42
it is a problem to show absent dates in sql query. You need or write a procedure or add them in php — splash58, Apr 30 '16 at 14:44

score 0 · Answer 1 · answered Apr 30 '16 at 14:53

0

If order value is a number of orders that day it should have been defaulted to 0 in MySQL table. If it's not it's an error and should be fixed but let's assume you can't change it, in that case you can do it at MySQL query level using COALESCE function.

Let's say your table is orders_stats and order_value is integer.

SELECT `order_date`, COALESCE(`order_value`, 0) as `order_value` FROM `orders_stats`;

This query works the same way as:

SELECT `order_date`, `order_value` FROM `orders_stats`;

the difference is order_value will be set to 0 if it's null.

More on COALESCE can be found on mysql.com and w3resource.com.

answered Apr 30 '16 at 14:53

Linek

1,353
10
20

what if the record for any date is not available in table ? I want such date in output with date and 0 value – Bhavesh Patel Apr 30 '16 at 15:03
@BhaveshPatel Here is an answer to your question [stackoverflow.com](http://stackoverflow.com/q/3538858/2008285) – Linek Apr 30 '16 at 15:09
thanks for quick answer @Linek. I will check this solution tonight – Bhavesh Patel Apr 30 '16 at 15:24

one mysql query for all count of orders on each day

1 Answers1