SQL - Joining two tables and counting items

Question

Alright, I'm trying to see how many products are provided by each supplier (Products and Suppliers being separate tables). I want the results to be displayed with the company name and the number of products that company has available. I'm not sure exactly how to set this up.

So far I have:

SELECT CompanyName, Count(ProductName) FROM Suppliers 
left join Products on Suppliers.SupplierID = Products.SupplierID;

I'm not sure how to make the ProductName count specific to each company. I'd be eternally grateful for any help you might be able to provide me.

Answer 1

All you are missing is a GROUP BY clause:

SELECT CompanyName, Count(ProductName)
  FROM Suppliers LEFT JOIN Products
    ON Suppliers.SupplierID = Products.SupplierID
 GROUP BY CompanyName;

The use of LEFT {OUTER} JOIN means that if there are suppliers that do not provide any products, then the join will return a set of NULL values for the columns corresponding to the Products table. The COUNT(ProductName) then counts just the number of non-null values, thus producing the answer 0 for companies that provide no products. Very often, you'd use a regular INNER JOIN and then you'd not see the companies that provide no products.