CODEWITHSUNDEEP
mysqlsql
Webgen
Webgen

Reputation: 25

Getting most sold products complex sql

TABLE STRUCTURES:

Orders OrderID, OrderStatusID, ......
OrderDetails ODID,ODProductID, ODProductQty, ODOrderID
Products ProductID, ProductName...

MY SQL so far:

SELECT * FROM Products 
INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ODProductID 
INNER JOIN Orders ON OrderDetails.ODOrderID = Orders.OrderID 
where Orders.OrderStatusID = '5'

OrderStatusID must be 5 (For Delivered Order).

I just want result to be ProductName & NoOfProducts of most sold product.

Can be like top 4 products sold.

Upvotes: 0

Views: 71

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You need aggregation, but you need a definition of "most sold". My guess is that this is based on quantity:

SELECT p.ProductID, p.ProductName, SUM(od.ODProductQty) as TotalQty
FROM Products p INNER JOIN
     OrderDetails od
     ON p.ProductID = od.ODProductID INNER JOIN
     Orders o
     ON od.ODOrderID = o.OrderID 
WHERE Orders.OrderStatusID = 5
GROUP BY p.ProductID, p.ProductName
ORDER BY TotalQty DESC
LIMIT <n>;

Upvotes: 0

forpas
forpas

Reputation: 164089

You must group by product and aggregate.
Then sort the results descending for the number of products sold and limit the results to 4 products:

SELECT p.ProductID, p.ProductName, COUNT(DISTINCT d.ODID) NoOfProducts 
FROM Products p 
INNER JOIN OrderDetails d ON p.ProductID = d.ODProductID 
INNER JOIN Orders o ON d.ODOrderID = o.OrderID 
WHERE o.OrderStatusID = '5'
GROUP BY p.ProductID, p.ProductName
ORDER BY NoOfProducts DESC
LIMIT 4

Upvotes: 1

Related Questions