CODEWITHSUNDEEP
mysqlsql
Warren
Warren

Reputation: 2014

MySQL - include zero count for date

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

Upvotes: 0

Views: 516

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 0

shmosel
shmosel

Reputation: 50716

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

Upvotes: 1

Related Questions