CODEWITHSUNDEEP
mysqlsql
JohnRW
JohnRW

Reputation: 778

MySQL - Matching ID to Max of Count ("Need" more elegant solution)

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

Answers (2)

valex
valex

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

bowlturner
bowlturner

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

Related Questions