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
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;
1
  • hey thanks, its mysql but this helps out a lot. I should be able to convert it - thank you Oct 30 '13 at 22:04
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, privacy policy and cookie policy

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