Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

What it means

GROUP BY shopper_id turns all of a shopper’s rows into one. Each column in the result must then have one value per group: either a column you grouped by, or an aggregate (SUM, COUNT, MAX…) computed over the group. A plain column such as placed_at has several values in a group, so SQL Server refuses the query before running it:

Column 'seo_sqlerr_data.purchases.placed_at' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The message names the column as schema.table.column, even when you wrote an alias such as s.name. The same rule applies outside the select list, with its own number:

• 8127: Column "…" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
• 8121: Column '…' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Common causes

1. A descriptive column next to an aggregate: SELECT s.id, s.name, SUM(p.total) … GROUP BY s.id. PostgreSQL accepts this when s.id is the primary key, and MySQL’s default mode does too; SQL Server doesn’t, even then.
2. Wanting a value from one particular row of the group, such as the date of the latest order. No aggregate says “the row with the latest date”; that needs a window function.
3. One aggregate in the select list makes the whole query grouped. SELECT shopper_id, total * 100 / SUM(total) FROM purchases has no GROUP BY but still fails, because SUM collapses all rows into one.
4. Ordering or filtering by an ungrouped column: ORDER BY placed_at or HAVING total > 10 (8127 and 8121).
5. A query ported from MySQL, written for a server where ONLY_FULL_GROUP_BY was off and any value from the group was returned.

How to fix it

Add the column to GROUP BY

If the column has one value per group anyway (a name that belongs to an id), group by it too:

SELECT s.id, s.name, SUM(p.total) AS spent
FROM dbo.shoppers AS s
JOIN dbo.purchases AS p ON p.shopper_id = s.id
GROUP BY s.id, s.name;

Aggregate it

If any of the group’s values will do, or you want the largest or smallest, wrap it:

SELECT shopper_id, MAX(placed_at) AS last_order, SUM(total) AS spent
FROM dbo.purchases
GROUP BY shopper_id;

HAVING filters groups, so it needs an aggregate too (HAVING SUM(total) > 10); to filter rows before grouping, use WHERE total > 10.

Keep every row and add the group’s total: a window function

SUM(…) OVER (PARTITION BY …) computes the aggregate per group without collapsing the rows:

SELECT id, shopper_id, total,
       SUM(total) OVER (PARTITION BY shopper_id) AS shopper_spent
FROM dbo.purchases;

Get one whole row per group: ROW_NUMBER

For the latest purchase of each shopper, with all its columns:

SELECT id, shopper_id, total, placed_at
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY shopper_id ORDER BY placed_at DESC) AS rn
  FROM dbo.purchases
) AS p
WHERE rn = 1;

Aggregate first, then join

To add details from another table, group in a derived table and join it to the details:

SELECT s.id, s.name, t.spent
FROM dbo.shoppers AS s
JOIN (SELECT shopper_id, SUM(total) AS spent FROM dbo.purchases GROUP BY shopper_id) AS t
  ON t.shopper_id = s.id;

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, tables shoppers (id, name) and purchases (id, shopper_id, total, placed_at) in a scratch schema:

SELECT shopper_id, placed_at, SUM(total) AS spent
FROM seo_sqlerr_data.purchases
GROUP BY shopper_id;

SELECT s.id, s.name, SUM(p.total) AS spent
FROM seo_sqlerr_data.shoppers AS s
JOIN seo_sqlerr_data.purchases AS p ON p.shopper_id = s.id
GROUP BY s.id;

Msg 8120, Level 16, State 1, Server 7732422b7f56, Line 1
Column 'seo_sqlerr_data.purchases.placed_at' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Server 7732422b7f56, Line 1
Column 'seo_sqlerr_data.shoppers.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The second fails although s.id is the primary key of shoppers. Ordering and filtering by ungrouped columns:

Msg 8127, Level 16, State 1, Server 7732422b7f56, Line 4
Column "seo_sqlerr_data.purchases.placed_at" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8121, Level 16, State 1, Server 7732422b7f56, Line 4
Column 'seo_sqlerr_data.purchases.total' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT shopper_id, total, total * 100 / SUM(total) FROM … with no GROUP BY failed with 8120 on shopper_id. Each fix above returned the expected rows; the ROW_NUMBER query returned purchase 2 (35.50 on 3 October) for shopper 1 and purchase 3 for shopper 2. SQL Server 2019 and 2025 printed the same messages.

In Inlet

When SQL Server refuses, Inlet shows its message with SQL Server error 8120, state 1, severity 16 and links to this page. With your own Anthropic API key, Ask Claude (⌘L) can rewrite the query with the right GROUP BY or a window function; it sends the schema and the query, never rows.