CODEWITHSUNDEEP
mysql
Bhavin
Bhavin

Reputation: 2158

Count result of Sub-query and Use it in Query

I have two Tables information_business and business_likes.

Table-1 :- information_business has fields id , name , address , dt_added etc.

Table-2 :- business_likes has fields id , business_id , user_id , status(1=> like , 0 => unlike)

If Someone like a business I'm inserting new data in business_likes table as a business_id, user_id and status = 1 and if the same person dislikes than that row are updated.

Now my question is I want to select all the business with the highest count of likes is first and lowest count of likes is last.

If any business has zero counts then all the businesses with zero counts are in last.

I tried so many queries but nothing works for me.

Query: 

SELECT *, (SELECT COUNT(status)
FROM business_likes) AS business_likes_count
FROM `business_likes`
LEFT JOIN information_business ON information_business.id = business_likes.business_id
ORDER BY business_likes.id

Upvotes: 0

Views: 23

Answers (1)

Suchit kumar
Suchit kumar

Reputation: 11859

Try this sql: you can use group by on business id and ORDER BY business_likes_count DESC if you want highest value to come first.

     SELECT information_business.*,COUNT(status) as business_likes_count FROM `business_likes` JOIN information_business 
ON information_business.id= business_likes.business_id group by  business_likes.business_id ORDER BY business_likes_count DESC;

Upvotes: 2

Related Questions