CODEWITHSUNDEEP
phpmysqlcodeigniter-3
Jaymin
Jaymin

Reputation: 1661

How Can I get Most popular products from order table?

I have my orders table like these

 Id  | Order_id | item_id | restaurant_id | price
--------------------------------------------------
  1 |  1        |  1      |   2            | 200
---------------------------------------------------
  2 |  2         |  1      |  2             | 200
---------------------------------------------------
  3 |  3        |  2       |  2             | 400

How can I get popular items from orders table in ascending order where restaurant_id is 2. I want something like these Popular items

1 
2

I try but I only get total counts and I am stuck over there

SELECT COUNT(item_id) FROM `item_orders` WHERE restaurant_id = 1

Upvotes: 1

Views: 393

Answers (2)

KOUSIK MANDAL
KOUSIK MANDAL

Reputation: 2052

Try this;

select * from 
(SELECT COUNT(item_id) as count FROM `item_orders` 
WHERE restaurant_id = 1) as T 
order by `count` asc

Upvotes: 1

Stefano Zanini
Stefano Zanini

Reputation: 5916

This way you get the count for each item and order them by count

SELECT  item_id, COUNT(*)
FROM    `item_orders`
WHERE   restaurant_id = 1
GROUP BY item_id
ORDER BY COUNT(*) DESC

Upvotes: 2

Related Questions