MySQL - include zero count for date

Question

There's a few questions like this, but I can't find the answer to my specific problem.

Consider the below table:

|                DateOfSale |         food | customerid |
|---------------------------|--------------|------------|
| January, 03 2017 00:00:00 |        pizza |          1 |
| January, 03 2017 00:00:00 |        pizza |          2 |
| January, 03 2017 00:00:00 |        pizza |          3 |
| January, 03 2017 00:00:00 | fish & chips |          4 |
| January, 02 2017 00:00:00 |        pizza |          4 |
| January, 02 2017 00:00:00 |        pizza |          4 |
| January, 02 2017 00:00:00 |        pizza |          5 |
| January, 02 2017 00:00:00 | fish & chips |          6 |
| January, 01 2017 00:00:00 |        pizza |          7 |
| January, 01 2017 00:00:00 |        pizza |          8 |
| January, 01 2017 00:00:00 |        pizza |          9 |

We're selling pizzas and fish & chips.

You'll notice that we didn't sell any fish & chips on Jan 1 in the below result, but I want to see a 0 sales count for this, but I don't. Also, there are no sales recorded for Jan 4, and I want to see 0 for both pizza and fish & chips. This is why I get dates from a date reference table - so that the date exists even if the sales don't.

|                DateOfSale |         food | Sales |
|---------------------------|--------------|-------|
| January, 01 2017 00:00:00 |        pizza |     3 |
| January, 02 2017 00:00:00 | fish & chips |     1 |
| January, 02 2017 00:00:00 |        pizza |     3 |
| January, 03 2017 00:00:00 | fish & chips |     1 |
| January, 03 2017 00:00:00 |        pizza |     3 |

The code for this result is:

SELECT DATE(d.thedate) as DateOfSale, f.food, count(s.customerid) as Sales
FROM ref_date d
LEFT JOIN sales s on d.thedate = s.saledate
INNER JOIN foods f on s.foodid = f.id
GROUP BY DATE(d.thedate), f.food

And the full fiddle with data is here: http://sqlfiddle.com/#!9/ff9dc/2

score 1 · Answer 1 · answered Jan 12 '17 at 00:42

Here you go:

SELECT d.thedate AS DateOfSale, f.Food, COUNT(s.customerid) AS Sales
FROM ref_date d
JOIN foods f
LEFT JOIN sales s ON s.saledate = d.thedate AND s.foodid = f.id
GROUP BY DateOfSale, Food;

Output:

|                   thedate |         food | Sales |
|---------------------------|--------------|-------|
| January, 01 2017 00:00:00 | fish & chips |     0 |
| January, 01 2017 00:00:00 |        pizza |     3 |
| January, 02 2017 00:00:00 | fish & chips |     1 |
| January, 02 2017 00:00:00 |        pizza |     3 |
| January, 03 2017 00:00:00 | fish & chips |     1 |
| January, 03 2017 00:00:00 |        pizza |     3 |
| January, 04 2017 00:00:00 | fish & chips |     0 |
| January, 04 2017 00:00:00 |        pizza |     0 |

SQL Fiddle

score 0 · Accepted Answer · edited Jan 12 '17 at 00:58

0

You can cross join foods with all possible dates and then left join the sales table to get a row with 0 count.

SELECT DATE(d.thedate) as DateOfSale, f.food, count(s.customerid) as Sales
FROM ref_date d
cross join foods f
LEFT JOIN sales s on s.saledate=d.thedate and s.foodid=f.id   
GROUP BY DATE(d.thedate), f.food
order by 1,2

edited Jan 12 '17 at 00:58

Strawberry

33,750
13
40
57

answered Jan 12 '17 at 00:42

Vamsi Prabhala

48,685
4
36
58

Thanks! I hate cross joins because they hurt my head. For anyone reading this who still confused as me, there's some good reading here: http://stackoverflow.com/questions/17759687/cross-join-vs-inner-join-in-sql-server-2008 – Warren Jan 12 '17 at 00:50

MySQL - include zero count for date

2 Answers2