SQL Server error 8120: Column not in GROUP BY or aggregate
A column in SELECT is neither in GROUP BY nor inside an aggregate like SUM or MAX, so SQL Server can't tell which row's value to show.
- Error number
8120- Severity
- 16
- Category
- Syntax and compilation
What it means
With GROUP BY, each output row stands for a group of input rows. A selected column must either be one of the grouping columns or be aggregated; otherwise there could be many different values per group. SQL Server enforces this strictly (unlike older MySQL modes, which picked an arbitrary value).
Common causes
- Adding a column to
SELECTand forgetting to add it toGROUP BY. - Wanting a value from "the latest row per group", which a plain
GROUP BYcan't express. - Grouping by an expression but selecting the underlying column.
How to fix it
-
Add the column to
GROUP BYif it identifies the group.SELECT CustomerId, Country, SUM(Total) AS total FROM dbo.Orders GROUP BY CustomerId, Country;
-
Aggregate it if any one value per group is fine.
SELECT CustomerId, MAX(OrderDate) AS last_order FROM dbo.Orders GROUP BY CustomerId;
-
For the whole row of the latest order per customer, use a window function.
SELECT * FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS rn FROM dbo.Orders AS o) AS x WHERE rn = 1;
Reproduce it safely
SELECT name, type, COUNT(*) FROM sys.objects GROUP BY type; -- Msg 8120 for 'name'
Related errors
8118Aggregate without GROUP BY42803PostgreSQL: column must appear in the GROUP BY clause1055MySQL: Expression isn't in GROUP BYORA-00979Oracle: not a GROUP BY expression
More syntax and compilation errors
102Incorrect syntax near156Incorrect syntax near the keyword105Unclosed quotation mark after the string4145Non-boolean expression in a condition8672MERGE touched the same row twice1934SET options have incorrect settings
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.