// SQL Server error reference

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

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 SELECT and forgetting to add it to GROUP BY.
  • Wanting a value from "the latest row per group", which a plain GROUP BY can't express.
  • Grouping by an expression but selecting the underlying column.

How to fix it

  1. Add the column to GROUP BY if it identifies the group.

    SELECT CustomerId, Country, SUM(Total) AS total
    FROM dbo.Orders
    GROUP BY CustomerId, Country;
  2. Aggregate it if any one value per group is fine.

    SELECT CustomerId, MAX(OrderDate) AS last_order
    FROM dbo.Orders
    GROUP BY CustomerId;
  3. 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

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.