SQL Server error 8118: Aggregate without GROUP BY
The SELECT mixes an aggregate such as COUNT(*) with a plain column and has no GROUP BY at all. Add a GROUP BY or aggregate the column.
- Error number
8118- Severity
- 16
- Category
- Syntax and compilation
What it means
A query with an aggregate and no GROUP BY returns a single row for the whole table. Any non-aggregated column in the select list would have to be one value out of many, so SQL Server rejects it with 8118.
Common causes
- Writing
SELECT name, COUNT(*) FROM ...and forgettingGROUP BY name. - Wanting the total next to each row, which needs a window aggregate rather than a plain one.
How to fix it
-
Group by the non-aggregated columns.
SELECT type, COUNT(*) AS n FROM sys.objects GROUP BY type;
-
To show a total on every row, use a window aggregate.
SELECT name, COUNT(*) OVER () AS total_objects FROM sys.objects;
Reproduce it safely
SELECT name, COUNT(*) FROM sys.objects; -- Msg 8118
Related errors
8120Column not in GROUP BY or aggregate42803PostgreSQL: column must appear in the GROUP BY clauseORA-00937Oracle: not a single-group group function
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.