Reputation: 778
Is there a better way to do the following:
SELECT ProductID, MAX(a.countProductID)
FROM
(
SELECT ProductID, COUNT(ProductID) as countProductID
FROM SalesOrderDetail
LEFT JOIN Product USING (ProductID)
GROUP BY ProductID
) as a
WHERE a.countProductID = (SELECT MAX(x.countProductID) FROM
(
SELECT ProductID, COUNT(ProductID) as countProductID
FROM SalesOrderDetail
LEFT JOIN Product USING (ProductID)
GROUP BY ProductID
) as x
);
Since im using the same subquery twice. However i can't access the first one from the WHERE clause.
Upvotes: 1
Views: 74
Reputation: 24144
I guess the task is to find product or products with the maximum sales count. First you shouldn't join with PRODUCT
table because all information you need is in SalesOrderDetail
table. Then use LIMIT 1
to find maximum count and HAVING
to select all products with maximum count:
SELECT ProductID, COUNT(ProductID) as countProductID
FROM SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(ProductID) = (SELECT COUNT(ProductID) as countProductID
FROM SalesOrderDetail
GROUP BY ProductID
ORDER BY countProductID DESC
LIMIT 1 )
Upvotes: 1
Reputation: 2016
The final answer
SELECT ProductID, COUNT(ProductID) as countProductID
FROM SalesOrderDetail
LEFT JOIN Product USING (ProductID)
GROUP BY ProductID
ORDER BY countProductID desc
LIMIT 1
Upvotes: 0