Group all the reservation record by the rooms.
select * from reservations group by room
For each room, count how many times it was booked.
select count(*) from reservations group by room
Also report the name of each room.
select t2.roomname, count(*) from reservations as t1 join rooms as t2 on t1.room = t2.roomid group by t1.room