'How do I select the count of records, then group them by a foreign key?

I have two tables: Products and Suppliers. SupplierID is a foreign key in products table. I need to make a table that contains country, supplierID, company name all from the supplier table then I need to have the number of products each supplier has. I tried the following query:

SELECT DISTINCT s.country, 
                s.supplierid, 
                s.companyname, 
                (SELECT Count(DISTINCT productid) AS 
                        "Number of Products Supplied" 
                 FROM   products 
                        INNER JOIN suppliers 
                                ON p.supplierid = s.supplierid) AS 
                "Number of products Supplied" 
FROM   suppliers s, 
       products p 
WHERE  ( s.country = 'usa' 
          OR s.country = 'uk' ) 
       AND s.supplierid = p.supplierid 

But I get the total number of products and not number of products supplied by each supplier. Any ideas?



Solution 1:[1]

SELECT s.country, s.supplierid, s.companycame, Count(*) AS Number_of_Products_Supplied
FROM suppliers s
 JOIN products p ON s.supplierid=p.supplierid
WHERE s.country IN ('usa', 'uk')
GROUP BY s.country, s.supplierid, s.companycame

I think that should do what you want, if I understood your question.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 DarrenMB