Grouping in my SQL for a constraint less than 10

1
votes

I am confused on which table I should use and or should I join the tables when attempting this question?

List total number of hotels in the database that have less than 10 rooms.

Hotel (hotelNo, hotelName, city)

Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

Guest (guestNo, guestName, guestAddress)

I have tried by just useing one table Room in my statement

SELECT hotelNo 
FROM Room
WHERE roomNo < 10
GROUP BY hotelNo;

Would this be correct or should I use something like this?

SELECT h.hotelNo,r.roomNo 
FROM Hotel h JOIN Room r ON h.hotelNo= r.hotelNo
WHERE r.roomNo < 10
GROUP BY hotelNo;
2
mysqlsql

2 Answers

1
votes

Assuming that all hotels have at least one room, you don't need a join. But, you do need aggregation:

select count(*)
from (select r.hotelno
      from rooms r
      group by r.hotelno
      having count(*) < 10
     ) r;

The subquery returns the hotels that have fewer than 10 rooms (and are in the rooms table, so they have at least one room).

The outer query counts the number of such hotels.

1
votes

Check count on the having in order to be applied to the groups, and that count the number of rows returned by that query

SELECT COUNT(*)
FROM (
    SELECT hotelRo
    FROM Room
    GROUP BY hotelNo
    HAVING COUNT(*)<10
) AS TMP;