Aggregating data by date in a date range without date gaps in result set

Question

I have a table with sell orders and I want to list the COUNT of sell orders per day, between two dates, without leaving date gaps.

This is what I have currently:

SELECT COUNT(*) as Norders, DATE_FORMAT(date, "%M %e") as sdate 
FROM ORDERS 
WHERE date <= NOW() 
  AND date >= NOW() - INTERVAL 1 MONTH 
GROUP BY DAY(date) 
ORDER BY date ASC;

The result I'm getting is as follows:

6     May 1
14    May 4
1     May 5
8     Jun 2
5     Jun 15

But what I'd like to get is:

6     May 1
0     May 2
0     May 3
14    May 4
1     May 5
0     May 6
0     May 7
0     May 8
.....
0     Jun 1
8     Jun 2
.....
5     Jun 15

Is that possible?

Answer 1

Creating a range of dates on the fly and joining that against you orders table:-

SELECT sub1.sdate, COUNT(ORDERS.id) as Norders
FROM
(
    SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY), "%M %e") as sdate 
    FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units
    CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens
    CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)hundreds
    WHERE DATE_SUB(NOW(), INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()
) sub1
LEFT OUTER JOIN ORDERS
ON sub1.sdate = DATE_FORMAT(ORDERS.date, "%M %e")
GROUP BY sub1.sdate

This copes with date ranges of up to 1000 days.

Note that it could be made more efficient easily depending on the type of field you are using for your dates.

EDIT - as requested, to get the count of orders per month:-

SELECT aMonth, COUNT(ORDERS.id) as Norders
FROM
(
    SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL months.i MONTH), "%Y%m") as sdate, DATE_FORMAT(DATE_SUB(NOW(), INTERVAL months.i MONTH), "%M") as aMonth 
    FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11)months
    WHERE DATE_SUB(NOW(), INTERVAL months.i MONTH) BETWEEN DATE_SUB(NOW(), INTERVAL 12 MONTH) AND NOW()
) sub1
LEFT OUTER JOIN ORDERS
ON sub1.sdate = DATE_FORMAT(ORDERS.date, "%Y%m")
GROUP BY aMonth