SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Accessing another table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

danboy4
Starting Member

2 Posts

Posted - 10/16/2012 :  06:59:08  Show Profile  Reply with Quote
Hey guys, I have no idea if i'm in the right section or even the right website but I just need a little help with some basic SQL i'm doing.

I'm trying to count how many rooms in each hotel and list hotel names and count how many room numbers there are for each hotel. I have a couple tables (HOTEL, ROOM) and currently i have this:

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


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.

I don't know if this is enough information or pointless but hopefully someone can help.
Thanks

EDIT: Here is what is produced by my statement:

H100 3
H200 5
H300 2

Here is some sample code from ROOM:

ROOM_NO, HOTEL_NO ,R_TYPE,R_PRICE
------- -------- ------ ---------
1 H100 S 220
2 H100 D 230
3 H100 F 310
1 H200 S 260
2 H200 D 170
3 H200 S 250
4 H200 F 180
5 H200 F 295
1 H300 D 200
2 H300 S 420

AND HOTEL:
HOTEL_NO H_NAME H_ADDRESS
------------------------------
H100 Sheraton Melbourne
H200 Shangra_La Sydney
H300 Hilton Perth

Edited by - danboy4 on 10/16/2012 07:12:34

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/16/2012 :  07:02:09  Show Profile  Reply with Quote
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.
Can you explain this in a bit more detail with example data? The statement seems to be self-contradictory.
Go to Top of Page

sql-programmers
Posting Yak Master

USA
190 Posts

Posted - 10/16/2012 :  08:01:31  Show Profile  Visit sql-programmers's Homepage  Reply with Quote
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/
Go to Top of Page

danboy4
Starting Member

2 Posts

Posted - 10/16/2012 :  09:27:35  Show Profile  Reply with Quote
Thanks! This has helped me understand the concept.
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/16/2012 :  09:32:48  Show Profile  Reply with Quote
simply this would do

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







------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000