Scooter Daraf Scooter Daraf - 3 months ago 7
MySQL Question

Get most favorit rooms mysql

I have this data in my database which i want get most room_id got in favorit by users .

Tablename:

favorit_rooms


id | userid | room_id| favorit

1 | 114 | 1 | 1
2 | 45 | 1 | 0
3 | 45 | 5 | 1
4 | 47 | 1 | 1
5 | 114 | 3 | 1
6 | 120 | 1 | 1
7 | 114 | 2 | 1
8 | 45 | 2 | 1
9 | 45 | 3 | 1
10 | 45 | 12 | 1
11 | 131 | 1 | 0


I tryed to group by room_id and userid but didnt work . and then i want to order them by the most favorit to less .Of course make attention to that favorit is not 0 .

SELECT id,room_id,count(userid) as countusers FROM favorit_rooms
GROUP BY room_id,favorit
ORDER by room_id,favorit desc LIMIT 5


My wished result is :

room_id countusers

1 3
2 2
3 2
5 1
12 1


userid AND room_id are index together so there is no dublicates .

How do i accomplish this thanks .

jpw jpw
Answer

It looks like you just need to change the grouping a bit and add a where clause. This should do what you want:

SELECT room_id, count(userid) AS countusers 
FROM favorit_rooms 
WHERE favorit = 1
GROUP BY room_id
ORDER by countusers DESC LIMIT 5