CODEWITHSUNDEEP
sql
dgamma3
dgamma3

Reputation: 2371

SQL query assistance

I have this schema: 

Hotel (**hotelNo**, hotelName, city)
Room (**roomNo, hotelNo**, type, price)
Booking (**hotelNo, guestNo, dateFrom**, dateTo, roomNo)
Guest (**guestNo**, guestName, guestAddress)
** denotes primary keys

I have to complete this query:

I have this query, which isn't quite correct: 

SELECT r.hotelno, type, count(*)
FROM Hotel h, room r
WHERE h.hotelNo = r.hotelno
GROUP BY r.hotelNo, type;

This is what it outputs:

enter image description here

What am I doing wrong?

Upvotes: 1

Views: 259

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 753485

It looks as though you're seeking the type of room which has maximum number of bookings for rooms of a given type at each hotel - an aggregate (maximum) of another aggregate (count of bookings of room type).

Build it up piece-wise. The number of bookings of rooms of each type at each hotel:

SELECT r.hotelno, r.type, count(*) AS num_bookings
  FROM Booking AS b
  JOIN Room AS r ON b.hotelNo = r.hotelno AND b.roomNo = r.roomNo
 GROUP BY r.hotelNo, r.type;

Now, you need to know which room type has the maximum at each hotel. That has to be done in two stages:

  1. Find the maximum number of bookings at the hotel for any type.
  2. Find the room types with that maximum number.

The first stage is:

SELECT s.hotelno, MAX(num_bookings) AS max_bookings
  FROM (SELECT r.hotelno, r.type, count(*) AS num_bookings
          FROM Booking AS b
          JOIN Room AS r ON b.hotelNo = r.hotelno AND b.roomNo = r.roomNo
         GROUP BY r.hotelNo, r.type
       ) AS s
 GROUP BY s.hotelno;

The second stage uses both the previous results for a final answer:

SELECT t.hotelno, t.type
  FROM (SELECT r.hotelno, r.type, count(*) AS num_bookings
          FROM Booking AS b
          JOIN Room AS r ON b.hotelNo = r.hotelno AND b.roomNo = r.roomNo
         GROUP BY r.hotelNo, r.type) AS t
  JOIN (SELECT s.hotelno, MAX(num_bookings) AS max_bookings
          FROM (SELECT r.hotelno, r.type, count(*) AS num_bookings
                  FROM Booking AS b
                  JOIN Room AS r ON b.hotelNo = r.hotelno AND b.roomNo = r.roomNo
                 GROUP BY r.hotelNo, r.type
               ) AS s
         GROUP BY s.hotelno) AS m
    ON t.hotelno = m.hotelno AND t.num_bookings = m.max_bookings;

If your DBMS supports WITH clauses, you can write that more succinctly.

Upvotes: 1

Seph
Seph

Reputation: 8693

I think you're going to have to use an inner query to get this one working:

SELECT dbo.Hotel.hotelName, pop.type, pop.Count
FROM dbo.Hotel
    INNER JOIN (
        SELECT TOP 1 dbo.Hotel.hotelNo, dbo.Room.type, COUNT(*) AS Count
        FROM dbo.Hotel
            INNER JOIN dbo.Room ON dbo.Room.hotelNo = dbo.Hotel.hotelNo
            INNER JOIN dbo.Booking ON dbo.Booking.roomNo = dbo.Room.roomNo AND dbo.Booking.hotelNo = dbo.Hotel.hotelNo
        GROUP BY dbo.Hotel.hotelNo, dbo.Room.type
        ORDER BY Count DESC, dbo.Room.type
    ) AS pop ON pop.hotelNo = dbo.Hotel.HotelNo
ORDER BY dbo.Hotel.hotelName

Upvotes: 0

IAmTimCorey
IAmTimCorey

Reputation: 16757

If you are looking for popularity, you would need to take into account the Booking table. Add the Booking table to your FROM statement, link on hotelNo and roomNo and do a count on the Booking table. This should give you the counts you want.

Edit: Here is some sample code for you (tested):

SELECT TOP (100) PERCENT dbo.Hotel.hotelName, dbo.Room.type, COUNT(*) AS Count
FROM dbo.Booking INNER JOIN
dbo.Room ON dbo.Booking.roomNo = dbo.Room.roomNo AND dbo.Booking.hotelNo = dbo.Room.hotelNo 
INNER JOIN dbo.Hotel ON dbo.Room.hotelNo = dbo.Hotel.hotelNo
GROUP BY dbo.Hotel.hotelName, dbo.Room.type
ORDER BY Count DESC

Upvotes: 1

Related Questions