CODEWITHSUNDEEP
mysqlsql
Vinay
Vinay

Reputation: 7666

Find most purchased product

Below is a simplified version of my problem. Suppose I have a 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 by user we would insert a row in orders table along with the product's ID and how many units he ordered

I want to get the list of products in descending order of their purchase ie. starting from most purchased to the least one. But the problem is ranking depends not only on how many times it was purchased by users (row count) but also on how many units where purchased in that single order.

here is what I have tried

SELECT products.name 
FROM orders left join products
ON ( orders.product_id = products.id )
GROUP by orders.product_id
ORDER by count(orders.product_id)  desc;

which is clearly wrong as it gives 

chocolate
pepsi
apple

rather than 

apple
pepsi
chocolate

Regards

Upvotes: 1

Views: 78

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43564

You want to ORDER BY the SUM of quantity:

SELECT products.name, SUM(orders.quantity) AS sum_quantity
FROM orders LEFT JOIN products ON orders.product_id = products.id
GROUP BY orders.product_id
ORDER BY sum_quantity DESC;

demo (with comparison of SUM and COUNT): http://sqlfiddle.com/#!9/b1ec57/2/0

You are using COUNT at the moment. So you only get the number of orders but not the quantity of ordered products.

Upvotes: 2

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can use GROUP BY with SUM, e.g.:

SELECT name
FROM products
WHERE id IN (
 SELECT product_id
 FROM orders
 GROUP BY producy_id
 ORDER BY SUM(quantity) DESC
);

Upvotes: 1

Related Questions