CODEWITHSUNDEEP
mysql
Kareem Mohamed
Kareem Mohamed

Reputation: 251

Select top sales products

I have three tables like this

orders(id, status, ...)
products(id, created_at, ...)
product_order(order_id, product_id, quantity)

I want to select the most sold products first then continue with latest products taking the quantity in consideration, Here's my try

SELECT products.* FROM products
LEFT JOIN product_order ON product_order.product_id = products.id
LEFT JOIN orders ON orders.id = product_order.order_id
WHERE orders.status != 'REJECTED'
GROUP BY product_order.product_id
ORDER BY COUNT(*) DESC, products.created_at

This statement returns the products that are not sold first because I am using left join and they count more than the sold ones.. also I don't know how to take the quantity in consideration

Thank you,

Upvotes: 1

Views: 774

Answers (2)

kmas
kmas

Reputation: 6439

This should work :

SELECT p.*, sum(po.quantity) qty
FROM products p
LEFT OUTER JOIN product_order po ON po.product_id = p.id
LEFT OUTER JOIN orders        o  ON o.id          = po.order_id
WHERE o.status != 'REJECTED'
GROUP BY po.product_id
ORDER BY qty DESC, p.created_at

Upvotes: 2

Mike
Mike

Reputation: 2549

If you want the most sold products you could add

AND products.quantity = SELECT max(quantity) from products

after your WHERE statement

Upvotes: 0

Related Questions