CODEWITHSUNDEEP
mysqlsqldategroup-bycount
Anthony Tata
Anthony Tata

Reputation: 33

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

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

Upvotes: 2

Views: 457

Answers (1)

GMB
GMB

Reputation: 222482

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.

Upvotes: 2

Related Questions