CODEWITHSUNDEEP
mysqlsqldatabase
tarek fellah
tarek fellah

Reputation: 367

Get most sold products with a specified one

I have a table order_detail with this informations

id_order  id_product
1         2
1         3
2         2
2         4
2         5
2         3
3         2
3         1
4         2 
4         3
4         1
4         6

I would like to get most sold products(the first 5 elements) with the current product let's say product id 2

I tried this but it returns one wrong result 

SELECT od2.product_id, count(od2.`product_id`) FROM `ps_order_detail` od1  
LEFT JOIN ps_order_detail od2 ON od1.id_order = od2.id_order where
od2.product_id != od1.product_id AND od1.product_id=2

The result should be 

product_id    count(od2.`product_id`)
3             3
4             2
1             1
5             1 
6             1

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Your query is on the right track. Mostly, you are missing a group by:

select od.product_id, count(od2.id_order) as NumTimesWith2
from ps_order_detail od left join
     ps_order_detail od2
     on od.id_order = od2.id_order and
        od2.product_id = 2 
where od.product_id <> 2
group by od.product_id
order by count(od2.id_order) desc;

If you want only one such product, then add a limit 1 to the query.

Also, this assumes that products are not repeated inside orders. If they can be, you can quickly get a better count using count(distinct od.id_order)).

Upvotes: 1

Related Questions