MYSQL return zero in date not present, and COUNT how many rows are present with a specific date

user_11396172 0
Viewed 170

I have a table named calendar with a single column (mydate DATE).

MYDATE
2020-01-01
2020-01-02
2020-01-03

...

I have also a table named delivery with three columns (id PK, giorno DATE, totale FLOAT)

ID    GIORNO          TOTALE
1     2020-01-01      0.10
2     2020-01-01      5
3     2020-01-02      12
4     2020-01-12      5
5     2020-02-02      13.50

This is what I'm trying to obtain:

Day            Numbers of orders
2020-01-01     2
2020-01-02     1
2020-01-03     0
2020-01-04     0
2020-01-05     0
2020-01-06     0
2020-01-07     0
2020-01-08     0
2020-01-09     0
2020-01-10     0
2020-01-11     0
2020-01-12     1

...

I was trying this query:

SELECT c.mydate, IFNULL(d.totale, 0) value 
FROM delivery d
RIGHT JOIN calendar c
    ON ( c.mydate = d.giorno )
GROUP BY c.mydate 
ORDER BY c.mydate
count group-by date mysql sql
1 Answers

Consider:

select c.mydate, count(d.id) number_of_orders
from calendar c
left join delivery d on d.giorno = c.mydate
group by c.mydate

This works by left-joining the calendar table with the orders table, then aggregating by date, and finally counting the number of matching rows in the order table.

This is quite close to your original query (although this uses left join instead of right join), however this uses an aggregate function to count the orders.

user_106767160
Related