mysql - Find most purchased product -

below simplified version of problem. suppose have products table

products ------------- id      name         1       chocolate        2       pepsi        3       apple        4       chips        

and orders table

orders ------------------------------------------- id      product_id      quantity    user_id 1           1           2               1 2           1           2               2 3           1           2               3 4           1           2               4 5           2           5               5 6           2           5               6 7           3           20              7 

for every purchase user insert row in orders table along product's id , how many units ordered

i want list of products in descending order of purchase ie. starting purchased least one. problem ranking depends not on how many times purchased users (row count) on how many units purchased in single order.

here have tried

select products.name  orders left join products on ( orders.product_id = products.id ) group orders.product_id order count(orders.product_id)  desc; 

which wrong gives

chocolate pepsi apple 

rather

apple pepsi chocolate 

regards

you want order by sum of quantity:

select products.name, sum(orders.quantity) sum_quantity orders left join products on orders.product_id = products.id group orders.product_id order sum_quantity desc; 

demo (with comparison of sum , count): http://sqlfiddle.com/#!9/b1ec57/2/0

you using count @ moment. number of orders not quantity of ordered products.