// SQL Server error reference

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
Error message
Column '%.*ls.%.*ls' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

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 forgetting GROUP BY name.
  • Wanting the total next to each row, which needs a window aggregate rather than a plain one.

How to fix it

  1. Group by the non-aggregated columns.

    SELECT type, COUNT(*) AS n FROM sys.objects GROUP BY type;
  2. 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

More syntax and compilation errors

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.

// fewer errors, faster fixes

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.