Mysql – How to get a list/result of best selling items

MySQL

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

Best Answer

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;
Related Question