count(distinct(col_name)) not working in my sql

Question

I am newbie in mysql, for learning i am using sample database of w3schools. in that i want to create a view which will for each supplier, list the supplier name, the count of distinct products by the supplier (PCount), the count of distinct product categories by the supplier (CCount).

here is my table structures

Suppliers                             Products
--------------------            ----------------------------
SupplierID                             ProductID
SupplierName                           ProductName  
ContactNumber                          SupplierID
Address                                CategoryID
City                                   Unit  
PostalCode                             Price
Country
Phone

What i have try is

SELECT s.SupplierName, COUNT(DISTINCT(p.ProductID)) as PCount, COUNT(DISTINCT(p.CategoryID)) as CCount FROM Suppliers as s 
right join Products as p 
on s.SupplierID = p.SupplierID

this is only giving me single SupplierName not all the supplier details with distinct product and distinct category.

Any help would be much appreciated.

Answer 1

Give this a shot:

SELECT s.SupplierName,
    COUNT(p1.ProductID) AS PCount,
    COUNT(DISTINCT p2.CategoryID) AS CCount
FROM Suppliers s
    LEFT JOIN Products p1 ON s.SupplierID=p1.SupplierID
    LEFT JOIN Products p2 ON s.SupplierID=p2.SupplierID
GROUP BY s.SupplierName
ORDER BY s.SupplierName