CODEWITHSUNDEEP
sqlgroup-by
ZPC
ZPC

Reputation: 15

SQL. most commonly booked room type

My tables:

 1. Hotel - city, hotelNo[pk]
 2. Room - roomNo[pk], type, hotelNo[fk]
 3. Booking - roomNo[fk], hotelNo[fk],date

And I want to show the most commonly booked room type for each hotel in London(hotel.city='London')

The code I found from the Internet were all like this

SELECT hotelno, type, MAX(y)
FROM
(SELECT hotelno, type, COUNT(type) AS y
FROM booking b, hotel h, room r
WHERE r.roomno = b.roomno AND
r.hotelno = b.hotelno AND
b.hotelno = h.hotelno AND
city = 'London'
GROUP BY hotelno, type)
GROUP BY hotelno, type;

It returns three columns hotelno, type and max(y). And all the booking times of each type of each hotel. I just want gets the most booking one type for each hotel. How can I do this? Thanks for answering.

Upvotes: 0

Views: 1241

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76561

I have rewritten my answer, to fix all the problems. This is the query:

SELECT hotelno, type
FROM
(SELECT hotelno, type, COUNT(type) AS y
FROM booking b, hotel h, room r
WHERE r.roomno = b.roomno AND
r.hotelno = b.hotelno AND
b.hotelno = h.hotelno AND
city = 'London'
GROUP BY hotelno, type) t
where not exists
(SELECT 1
FROM booking b2, hotel h2, room r2
WHERE r2.roomno = b2.roomno AND
r2.hotelno = b2.hotelno AND
b2.hotelno = h2.hotelno AND
h2.city = 'London'
GROUP BY b2.hotelno, r2.type
having b2.hotelno = t.hotelno and count(*) > y);

Basically I do the same grouping in the where as in the from.

Upvotes: 1

Related Questions