SQL combining the join result of two different tables

I am having a problem with SQL at the moment.

I use an example database of W3School

This is what I have now:

SELECT 
    Categories.CategoryID, 
    Categories.CategoryName, 
    COUNT(Products.CategoryID) AS CountProduct
FROM 
    Categories
INNER JOIN 
    Products ON Products.CategoryID = Categories.CategoryID
GROUP BY
    CategoryName
ORDER BY 
    CategoryName ASC

Output:

CategoryID  CategoryName    CountProduct
-----------------------------------------
1           Beverages           12
2           Condiments          12
3           Confections         13
4           Dairy Products      10
5           Grains/Cereals      7
6           Meat/Poultry        6
7           Produce             5
8           Seafood             12

And this:

SELECT 
    Categories.CategoryID, 
    Categories.CategoryName, 
    COUNT(OrderDetails.ProductID) AS CountOrder
FROM 
    Categories
LEFT JOIN 
    Products ON Products.CategoryID = Categories.CategoryID
LEFT JOIN 
    OrderDetails ON OrderDetails.ProductID = Products.ProductID
GROUP BY
    CategoryName
ORDER BY 
   CategoryName ASC

Output:

CategoryID  CategoryName    CountOrder
------------------------------------------------
1           Beverages           93
2           Condiments          49
3           Confections         84
4           Dairy Products      100
5           Grains/Cereals      42
6           Meat/Poultry        50
7           Produce             33
8           Seafood             67

I just want this result:

CategoryID  CategoryName    CountProduct    CountOrder
-------------------------------------------------------
1           Beverages           12              93
2           Condiments          12              49
3           Confections         13              84
4           Dairy Products      10              100
5           Grains/Cereals      7               42
6           Meat/Poultry        6               50
7           Produce             5               33
8           Seafood             12              67

I tried to combine two query into one but I get the wrong result.

Thank you

Answer

Since this is a learning session it seems like they are trying to teach you about Distinct count so use the below query,

SELECT Categories.CategoryID, Categories.CategoryName, COUNT(DISTINCT Products.PRODUCTID) as ProductCout, Count(OrderDetails.ProductID ) as CountOrder
FROM Categories
LEFT JOIN Products On Products.CategoryID = Categories.CategoryID
LEFT JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID
Group By Categories.CategoryID, Categories.CategoryName

Advertisement

Answer