MySQL: adding days with zero orders to the list of orders per day

Question

MySQL: adding days with zero orders to the list of orders per day

I have a table like this

Date | OrderID
2012-03-09 | 123
2012-03-09 | 122
2012-03-07 | 121

And I am making a request like this

SELECT Date, COUNT(OrderID) FROM Table GROUP BY Date

I want to include days with zero orders in this list.

Is there an elegant solution for this?

+3

mysql

Alex 09 Mar 12 at 15:50

source to share

1 answer

Marc B · Accepted Answer · 2012-03-09T15:53:38+0000

Assuming yours Table

is a table of orders then no. The query will not create rows / data that does not already exist in the database. If there are no order records for a specific day, you will not receive that exit date.

You can create a temporary table containing each date in a specific range and join it - then you really have all the dates you need and you get a 0-ordinal score for quiet days.

MySQL: adding days with zero orders to the list of orders per day

More articles: