T-SQL GROUP BY also using JOIN, Aggregate and Outer Reference errors
I'm learning SQL and trying to join several mock Hotel tables and make a simple query that shows a guest's concatenated name and the number of reservations they've made.
No matter how I try to restructure my query I keep getting one of two errors.
If I try this way:
SELECT [Guests].FirstName + ' ' + [Guests].LastName AS 'Guest Name', [ReservationsRooms].Reservation_ID FROM [ReservationsRooms] JOIN [Reservations] ON [ReservationsRooms].Reservation_ID = [Reservations].Reservation_ID JOIN [Guests] ON [Reservations].Guest_ID = [Guests].Guest_ID GROUP BY [Guests].Guest_ID
I get an error:
Column 'Guests.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I thought maybe it was an ambiguity issue, so I also tried as follows:
SELECT [Guests].FirstName + ' ' + [Guests].LastName AS 'Guest Name', COUNT([ReservationsRooms].Reservation_ID) AS 'Reservations' FROM [ReservationsRooms] JOIN [Reservations] ON [ReservationsRooms].Reservation_ID = [Reservations].Reservation_ID JOIN [Guests] ON [Reservations].Guest_ID = [Guests].Guest_ID GROUP BY [Guests].Guest_ID, 'Guest Name'
But this also resulted in an error:
Each GROUP BY expression must contain at least one column that is not an outer reference.
I am trying to make a query result that returns something like this:
Reservations Guest Name ------------------------------- 4 Mack Simmer 3 Bettyann Seery 2 Duane Cullison 2 Aurore Lipton 2 Maritza Tilton 2 Joleen Tison
Specifically, I'd like ONLY these two columns in my query but can't seem to find a way of doing it.
This is why I really dislike people using literal strings for aliases; the only place this works is in the
GROUP BY 'Guest Name'doesn't mean "Group by the column aliased as
'Guest Name'" it means "Group by the literal string
'Guest Name'", which, unsurprisingly doesn't make any sense.
Even if you had used a better delimit identifier for your alias, either T-SQL's Brackets (
) or ANSI-SQL's double quotes (
"),you still wouldn't be able to do what you did as you can't reference a column by it's alias in the
GROUP BY(for example
GROUP BY [Guest Name]would produce the error "Invalid column name 'Guest Name'"). This is due to the Logical Processing Order of the SELECT statement and that the
GROUP BYis processed several steps before the
SELECT(steps 5 and 8 respectively), and so the alias has no context within the
Instead you either need to
GROUP BYthe expression itself, or by the columns contained in the expression. I would personally use the latter here.
SELECT G.FirstName + ' ' + G.LastName AS GuestName, --Generally, avoid names that need delimit identification COUNT(RR.Reservation_ID) AS Reservations FROM dbo.ReservationsRooms RR --Always schema qualify. Also aliases are good. JOIN dbo.Reservations ON RR.Reservation_ID = R.Reservation_ID JOIN dbo.Guests G ON R.Guest_ID = G.Guest_ID GROUP BY G.FirstName, G.LastName, G.Guest_ID;
Alternatively, you could omit
GROUP BYentirely and wrap the expression for
MAX(G.FirstName + ' ' + G.LastName AS GuestName) AS GuestName