SQL Server error 8120: 'Column is invalid in the select list ... GROUP BY clause'

Column 'dbo.Orders.City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (error 8120) means the query groups rows but also asks for a column that can have more than one value per group. Add that column to GROUP BY, or wrap it in an aggregate such as MAX.

Every column in the select list of a grouped query must be either grouped or aggregated. Check them all, not only the one in the message: a test query with two ungrouped columns, City and Id, returned a single error 8120 that named only City.

The error

SELECT CustomerName, City, SUM(Amount) AS Total
FROM dbo.Orders
GROUP BY CustomerName;

Msg 8120, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
Column 'dbo.Orders.City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Why it happens

GROUP BY CustomerName collapses all of a customer's orders into one output row. For that row SQL Server knows one CustomerName and can compute SUM(Amount), but City is a value from each individual order. If two orders of the same customer had different cities, there would be no single right answer, so SQL Server refuses the query at compile time rather than picking one. It does this even when your data happens to have only one city per customer, as the test data did: the rule is checked against the query, not the rows.

The fix

-- fix A: group by every non-aggregated column
SELECT CustomerName, City, SUM(Amount) AS Total
FROM dbo.Orders
GROUP BY CustomerName, City
ORDER BY CustomerName;
GO
-- fix B: aggregate the column instead
SELECT CustomerName, MAX(City) AS City, SUM(Amount) AS Total
FROM dbo.Orders
GROUP BY CustomerName
ORDER BY CustomerName;

Both returned the same three rows on the test data: Amara Okafor, Lagos, 200.00; David Chen, Osaka, 45.00; Maria Garcia, Lisbon, 200.50. They are not the same query, though. Fix A gives one row per customer and city, so a customer with orders in two cities appears twice. Fix B keeps one row per customer and picks the alphabetically last city, which is only correct when the column truly has one value per group. Choose by what the report should mean. When you need details that do not fit either shape, compute the totals in a subquery or CTE grouped by the key, then join back to the table for the other columns.

How it was reproduced

One script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e8120.sql on SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16.0.1000.6, in a throwaway database fix8_lab created for the test and dropped afterwards. One orders table with four fictional rows; the failing query and both fixes ran in the same script.

Frequently asked

Why is my column invalid in the select list in SQL Server?

Error 8120 means the query has GROUP BY or an aggregate, and the named column is neither in GROUP BY nor inside an aggregate function, so it could have several values per output row.

Should I add the column to GROUP BY or use MAX?

Add it to GROUP BY when you want one row per combination of values. Use MAX or MIN only when the column has a single value per group, for example a name that depends on the grouped key.

Does error 8120 depend on the data in the table?

No. SQL Server checks the query, not the rows. Even when every customer has only one city, selecting City without grouping or aggregating it fails before any row is read.

More decoded errors in the Fixes category.