Z0q Z0q - 16 days ago 5
SQL Question

How to count best sold products in MySQL?

Let's say I have this table with order lines:

order_lines

- id

- order_id

- product_id

- quantity

It contains all lines per order which contain a quantity x of product y.

Now I want to calculate out of these order lines which products are best sold. I am trying to write a query which does this efficiently.

Maybe something similar to this? It's just a guess.

SELECT DISTINCT product_id, COUNT(SUM(quantity)) as q FROM order_lines ORDER BY q DESC


Any ideas?

Answer

Although "best sold" is subjective I will treat it as most sold product's for all given order's, In this case you need to do the SUM with Group By and it will give you the query.

SELECT product_id, SUM(quantity) As MostSold 
FROM order_lines 
Group By product_id
ORDER BY MostSold  DESC

And if you want highest number of orders for each product then you can go as,

SELECT product_id, Count(product_id) As MostSold 
FROM order_lines 
Group By product_id
ORDER BY MostSold  DESC