PostgreSQL – how to have the 0 values using a WHERE?

i am currently working on PostgreSQL having 2 data tables : item and order.

I want to display the number of order for each item in a period of time, but i would like to keep the name displayed if it’s 0.

i’m actually doing:

SELECT Item.name, count(Order.id) from Item
INNER JOIN Order ON Item.id = Order.it_id
WHERE Order.date > '01/08/2021'

The problem is that my results are:

Chocolate | 1
Cake | 2
Muffin | 3

And i would like to have:

Chocolate | 1
Cake | 2
Muffin | 3
Pancake | 0
Donut | 0

I have used a RIGHT JOIN that is working without my WHERE condition but i need to filter with the date for my results values.

Maybe i’m not in the good way to do it. I don’t know if it is possible or if there an other way to do it.

thank you in advance.

Answer

First, your query should return an error because it has no group by.

Second, you want a left join:

SELECT i.name, COUNT(o.id)
FROM Item i LEFT JOIN
     Order o
     ON i.id = o.it_id AND
        o.date >= '2021-01-08'
GROUP BY i.name;

Also notice that the date is in a standard format and the condition moves to the ON clause.