I am having a problem with SQL at the moment.
I use an example database of W3School
This is what I have now:
x
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
Advertisement
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