SQL Server Adding Count up

Question

I'm working on a query and I'm getting the result as intended but they're not adding up

My Query

SELECT SUBSTRING(h.HotelName, 1, CHARINDEX(' ', h.HotelName)) AS 'HotelName',       LEFT(r.RoomNumber, 1) AS Floor, COUNT(*) AS 'Rooms'
FROM HOTEL AS h
JOIN HOTELROOMTYPE AS hr ON h.HotelID = hr.HotelID
JOIN ROOM AS r ON hr.HotelRoomTypeID = r.HotelRoomTypeID
GROUP BY r.RoomNumber, h.HotelName

My Results

HotelName                      Floor Rooms
------------------------------ ----- -----------
John's                         2     1
John's                         2     1
John's                         3     1
University                     1     1
University                     1     1
University                     2     1
University                     2     1
University                     2     1
Utah                           2     1
Utah                           2     1
Utah                           2     1

Intended results

HotelName                      Floor Rooms
------------------------------ ----- -----------
John's                         2     2
John's                         3     1
University                     1     2
University                     2     3
Utah                           2     3

Basically I want the Rooms for each floor to be added up, any help would be great.

Gordon Linoff · Accepted Answer · 2014-09-30 14:29:34Z

4

If you want the floor in the output, then group by it:

SELECT SUBSTRING(h.HotelName, 1, CHARINDEX(' ', h.HotelName)) AS HotelName,
       LEFT(r.RoomNumber, 1) AS Floor, COUNT(*) AS 'Rooms'
FROM HOTEL h JOIN
     HOTELROOMTYPE hr
     ON h.HotelID = hr.HotelID JOIN
     ROOM r
     ON hr.HotelRoomTypeID = r.HotelRoomTypeID
GROUP BY h.HotelName, LEFT(r.RoomNumber, 1);

I presume none of your hotels have more than 9 floors.

answered Sep 30, 2014 at 14:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SaSquadge Over a year ago

Awesome worked! Thank you, and you assumed right hotel only has 3 floors :)

Collectives™ on Stack Overflow

SQL Server Adding Count up

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related