SQL Server Error 8120

Error number

8120

Severity

16 (user-correctable error)

State

1

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Syntax & Object Resolution

Last reviewed

23 October 2026

What it means

Error 8120 means a grouped query (GROUP BY, or an aggregate such as COUNT or SUM) selects a column that is neither grouped nor aggregated, so SQL Server has no single value to return for it.

Once a query groups rows, each output row stands for a whole group. Every column in the SELECT list must then be either one of the GROUP BY expressions (one value per group) or wrapped in an aggregate such as SUM, MAX or COUNT (one value computed from the group). A bare column that varies inside the group has many possible values, so SQL Server rejects the query at compile time with 8120 at severity 16. It does not pick a value for you, unlike some MySQL configurations.

The message names the column as schema.table.column, using the real table name even if you wrote an alias, for example dbo.Customers.name for c.name. An aggregate with no GROUP BY at all, such as SELECT customer_id, COUNT(*) FROM dbo.Orders, counts as grouping the whole table and raises the same error. When the offending column is in ORDER BY instead of SELECT, the engine raises the related error 8127.

Common causes

• A detail column (order date, product name, id) added to the SELECT list of a summary query without grouping or aggregating it.
• Grouping by a key column such as c.id but selecting a descriptive column such as c.name that is not in GROUP BY.
• An aggregate (COUNT, SUM, MAX) mixed with plain columns in a query that has no GROUP BY.
• A query ported from MySQL, where a permissive sql_mode returned an arbitrary value for non-grouped columns.
• Trying to fetch "the row with the latest date" using MAX() alone, when the other columns need a window function or a join.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

EXEC sp_describe_first_result_set
     N'SELECT customer_id, MAX(order_date) AS last_order, SUM(total) AS spent
       FROM dbo.Orders
       GROUP BY customer_id;';

SELECT c.id, COUNT(DISTINCT c.name) AS distinct_names
FROM dbo.Customers AS c
GROUP BY c.id
HAVING COUNT(DISTINCT c.name) > 1;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT c.id, c.name, COUNT(*) AS order_count
FROM dbo.Customers AS c
JOIN dbo.Orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name;

SELECT customer_id, MAX(order_date) AS last_order, SUM(total) AS total_spent
FROM dbo.Orders
GROUP BY customer_id;

SELECT id, customer_id, order_date, total
FROM (
    SELECT id, customer_id, order_date, total,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM dbo.Orders
) AS x
WHERE rn = 1;

Examples

A detail column in a summary query

CREATE TABLE dbo.Orders (id INT PRIMARY KEY, customer_id INT, order_date DATE, total DECIMAL(10,2));

SELECT customer_id, order_date, MAX(order_date) AS last_order
FROM dbo.Orders
GROUP BY customer_id;  -- order_date is neither grouped nor aggregated

Msg 8120, Level 16, State 1, Line 3
Column 'dbo.Orders.order_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Grouped by the key, selecting the name (the message shows the table, not the alias)

CREATE TABLE dbo.Customers (id INT PRIMARY KEY, name NVARCHAR(100));
CREATE TABLE dbo.Orders (id INT PRIMARY KEY, customer_id INT, total DECIMAL(10,2));

SELECT c.name, COUNT(*) AS order_count
FROM dbo.Customers AS c
JOIN dbo.Orders AS o ON o.customer_id = c.id
GROUP BY c.id;

Msg 8120, Level 16, State 1, Line 4
Column 'dbo.Customers.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

An aggregate with no GROUP BY

CREATE TABLE dbo.Orders (id INT PRIMARY KEY, customer_id INT, total DECIMAL(10,2));

SELECT customer_id, COUNT(*) AS orders
FROM dbo.Orders;  -- GROUP BY customer_id is missing

Msg 8120, Level 16, State 1, Line 3
Column 'dbo.Orders.customer_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How to prevent it

Write grouped queries from the GROUP BY outwards: decide what one output row represents, list those columns in GROUP BY, and then make every other selected column an aggregate. If you find yourself wanting "the other columns of that row", reach for a window function such as ROW_NUMBER instead.

For a refresher on how grouping, HAVING and ORDER BY fit together, see ORDER BY, GROUP BY and HAVING explained and the aggregate functions guide.

Common questions

Learn more on CodeWithSQL

• ORDER BY, GROUP BY and HAVING explained
• SQL aggregate functions explained
• SQL window functions guide

Official documentation

• GROUP BY (Transact-SQL)
• Database engine events and errors (8000 to 8999)
• sp_describe_first_result_set (Transact-SQL)

Last technically reviewed 23 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.