CODEWITHSUNDEEP
mysqlsql
user1379533
user1379533

Reputation:

What can I use if I want't to display a top 10 list of records

I have a a room booking system where, a user can choose a room, and then book timeslots for that given room.

I wan't to extend my system a bit, with the option to have a top 10 list of the most booked rooms.

You can see my database, and it's foreign keys here:

enter image description here

I know that I can count all of my bookings that have a room_id of 2 fx:

SELECT COUNT(*) FROM `bookings` WHERE `room_id` = 2

But what I want to achive, is having list that would look something like this:

Room1 = 84 bookings
Room2 = 70 bookings
Room3 = 54 bookings
etc.

Can anybody point me in the right direction?

Upvotes: 0

Views: 45

Answers (3)

Wit Wikky
Wit Wikky

Reputation: 1542

Try this query,

   select count(*) as booking_count , room_id as room from `bookings`
   group by room order by booking_count

Upvotes: 0

sshet
sshet

Reputation: 1160

I think you should use GROUP BY Clause

SELECT Count(*) as Count , room_id
FROM bookings
GROUP BY room_id
ORDER BY Count

Upvotes: 0

StanislavL
StanislavL

Reputation: 57391

SELECT COUNT(*) as bCount, room_id 
FROM `bookings`
GROUP BY room_id
ORDER BY bCount
LIMIT 10

Upvotes: 3

Related Questions