T-SQL GROUP BY also using JOIN, Aggregate and Outer Reference errors-CodePudding

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.

CodePudding user response：

This is why I really dislike people using literal strings for aliases; the only place this works is in the SELECT. 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 BY is processed several steps before the SELECT (steps 5 and 8 respectively), and so the alias has no context within the GROUP BY.

Instead you either need to GROUP BY the 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 G.FirstName and G.LastName from the GROUP BY entirely and wrap the expression for GuestName inside a MAX:

MAX(G.FirstName   ' '   G.LastName AS GuestName) AS GuestName