CODEWITHSUNDEEP
sqlpostgresql
tirth96
tirth96

Reputation: 45

PostgreSQL row not shown when count is zero

I am trying to output total number of orders placed by customers of each country in the year 2018. Here's my query:

select country, count(country) 
from customers, orders 
where customers.id = orders.id and orders.date >= '2018-01-01' and 
    orders.date <='2018-12-31' 
group by(country);

This prints out the correct values but countries with 0 orders are not outputted. I tried different types of joins as well (right join, left etc.) but still no luck. Anyone know how to fix this or know what might be wrong with it?

Upvotes: 1

Views: 1498

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use modern join not coma separated join, do count for orders.id 

select country, count(orders.id) 
from customers left join  orders 
on  customers.id = orders.id  and  orders.date >= '2018-01-01' and orders.date <='2018-12-31' 
group by country

You don't need parenthesis after group by

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

use left join and move your where condition in ON clause

select country, count(orders.id) 
from customers left join orders 
on customers.id = orders.id and orders.date >= '2018-01-01' and 
    orders.date <='2018-12-31' 
group by country

Upvotes: 2

Related Questions