2

I'm trying to get the results of my best selling products using SQL.

My table is order_items which has the following columns.

id|product_id|qty

As there is a QTY field I have to somehow count or group each by product ID then sum the qty, then only return the top 5 selling items.

So the data looks kind of like below.

id|product_id|qty
-------------------------
 1|   1      |   1
 2|   1      |   3 
 3|   2      |   3 
 4|   2      |   1 
 5|   1      |   7

... and so on

Not to sure about how to go about this, any help is much appreciated.

Database system used is MySQL

0

3 Answers 3

7

Assuming SQL Server, I might use:

SELECT TOP(5) ProductID, SUM(Quantity) AS TotalQuantity
FROM order_items
GROUP BY ProductID
ORDER BY SUM(Quantity) DESC;

This returns the top 5 best-selling Products.

Since SUM(Quantity) is also included in the SELECT clause, you can reference its alias in ORDER BY instead of repeating the expression:

...
ORDER BY TotalQuantity DESC;
0
6

Assuming MySQL:

SELECT product_id, SUM(qty) AS TotalQuantity
FROM order_items
GROUP BY product_id
ORDER BY SUM(qty) DESC
LIMIT 5

Here's SQLFIDDLE

-4
SELECT product_id, SUM(qty) AS TotalQuantity
FROM order_items
GROUP BY product_id
HAVING SUM(qty) DESC
LIMIT 10
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.