SELECT HOTEL_NO, COUNT(ROOM_NO) "NUMBER OF ROOMS"
FROM ROOM
GROUP BY HOTEL_NO
ORDER BY HOTEL_NO;

quote:

---

I need to be able to also select H_NAME (which lives in the table HOTEL) so it displays the Hotel Name and the number of available bookings, not the hotel number and the number of available bookings.

---

quote:

---

Originally posted by sql-programmers

Hi This may help you

;WITH NOOFROOMS AS
(
SELECT HOTEL_NO, COUNT(ROOM_NO) [NUMBER OF ROOMS]
FROM ROOM
GROUP BY HOTEL_NO
)
SELECT H_NAME, [NUMBER OF ROOMS] FROM NOOFROOMS
INNER JOIN HOTEL ON HOTEL.HOTEL_NO = NOOFROOMS.HOTEL_NO
ORDER BY H_NAME

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

---

SELECT h.H_NAME, r.NOOFROOMS 
FROM 
(SELECT HOTEL_NO,COUNT(ROOM_NO) AS NOOFROOMS 
FROM ROOM 
GROUP BY HOTEL_NO)r
INNER JOIN HOTEL h
ON h.HOTEL_NO = r.HOTEL_NO
ORDER BY h.H_NAME


or this if you're on SQL 2005 or above with compatibility level at least 90


SELECT DISTINCT h.H_NAME, COUNT(r.ROOM_NO) OVER (PARTITION BY h.HOTEL_NO) AS NOOFROOMS 
FROM ROOM r
INNER JOIN HOTEL h
ON h.HOTEL_NO = r.HOTEL_NO
ORDER BY h.H_NAME