I am trying to find the number of sales per petrol company. However I also want to include petrol companies that have made no sales but I cannot figure out how to do it. The stations table includes all the stations however sales only includes stations which actually had any sales.

This is how I am finding the number of sales per petrol station, but this doesnt include companies with 0 sales:

select stations.company,count(sales.sale) 
from stations 
   join sales on stations.id=sales.stationid 
group by stations.company;

My idea is to create a union with another query which just finds the companies with 0 sales but I don't know how to get a column with a 0 value in it. I tried to add having count(sales.sale) = 0 but since the stations with no sales just don't appear in the sales table that doesn't work.

I have looked at similar stack overflow questions but they all seem to reference using a different type of join however I have tried using left/right outer/inner joins with no luck.

CodePudding user response：

You haven't provided a minimum reproducible example so I can't be entirely certain, but seems like a case of needing a LEFT JOIN. Try the query below :

SELECT          stations.company, count(sales.sale) AS TotalSales
FROM            stations 
LEFT JOIN       sales ON stations.id=sales.stationid 
GROUP BY        stations.company