Show MySQL Count Including Zero Count with Group By Statement

Question

I am generating mysql query to show the number of orders received in current month group by days of month.

The table structure of mysql is as follow:

order_id   date   
========   ==========
1234       2012-07-02
1235       2012-07-02
1236       2012-07-04
1237       2012-07-07
1238       2012-07-08

Now I want it to return following results using mysql statement

count(order_id)   day
===============   ===
0                 01
2                 02
0                 03
1                 04
0                 05
0                 06
1                 07
1                 08

So on and so forth till the end of the month 30/31 depends on the days in month.

Looking forward to your suggestions and help.

Thanks.

[use this answer](http://stackoverflow.com/a/5081980/994054), make a left join, group by date, voila! generate `day` numbers (1,2,3,4...,29,30) in php — Peter, Apr 16 '14 at 20:21
possible duplicate of [MYSQL including values for dates with zero item counts](http://stackoverflow.com/questions/11237268/mysql-including-values-for-dates-with-zero-item-counts) — Marc B, Apr 16 '14 at 20:24

score 0 · Answer 1 · edited May 23 '17 at 12:05

SELECT
count(order_id),
dates.dte
FROM 
(
SELECT '2011-02-01' + INTERVAL a + b DAY dte
FROM
 (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
    UNION SELECT 8 UNION SELECT 9 ) d,
 (SELECT 0 b UNION SELECT 10 UNION SELECT 20 
    UNION SELECT 30 UNION SELECT 40) m
WHERE '2011-02-01' + INTERVAL a + b DAY  <  '2011-03-01'
ORDER BY a + b
) dates
LEFT JOIN
orders ON orders.`date` = dates.dte
GROUP BY
dates.dte
ORDER BY
dates.dte

generate day column values in your programming language

Thanks to @The Scrum Master for nice answer here

but I also agree this shouldn't be done in database. if you think about it you actually don't need rows with 0 count.

score 0 · Answer 2 · answered Apr 17 '14 at 05:09

0

Prepare the calendar table with a list of months and days.

To retrieve all orders from 2012-07:

select day(c.date), count(*)
from calendar c
left join orders o on c.date=o.date
where year(c.date) = 2012
and month(c.date) = 07
group by day(c.date)

answered Apr 17 '14 at 05:09

suz

737
2
9
22

Show MySQL Count Including Zero Count with Group By Statement

2 Answers2