Let's say I have this table with order lines:
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
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
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