CODEWITHSUNDEEP
phpmysqlsql
Z0q
Z0q

Reputation: 1893

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?

Upvotes: 1

Views: 1207

Answers (1)

Mahesh
Mahesh

Reputation: 8892

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

Upvotes: 3

Related Questions