SQL error

“Must Appear in the GROUP BY Clause” — SQL Error Fix

A safe portable rule is to group each selected non-aggregate column or aggregate it.

The exact message

DuckDB: Binder Error: column "product_name" must appear in the GROUP BY clause or must be part of an aggregate function.
PostgreSQL: column "products.product_name" must appear in the GROUP BY clause or be used in an aggregate function
SQL Server: Column 'products.product_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SQL Server: Each GROUP BY expression must contain at least one column that is not an outer reference.
Appears instead when the grouped expression only references a column from an enclosing query.
MySQL: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'products.product_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Only with ONLY_FULL_GROUP_BY enabled, which is the default from MySQL 5.7.5.

The symptom

You add an aggregate like count(*) and the query errors with something like "column must appear in the GROUP BY clause or be used in an aggregate function."

Why it happens

GROUP BY collapses many rows into one per group. The database needs one value for every selected expression. If a column is neither part of the grouping key nor wrapped in an aggregate, this query gives DuckDB no single value to return, so the binder rejects it.

The fix

Either add the column to GROUP BY, or wrap it in an aggregate (min, max, sum…).

The query that fails

select category_id, product_name, count(*)
from   products
group by category_id

Running it raises must appear in the GROUP BY clause.

The corrected query

select category_id, count(*) as product_count
from   products
group by category_id

Open the corrected query in the playground

Why it can happen with no GROUP BY clause

You can see the same error even when your query has no GROUP BY at all. If the SELECT list mixes one aggregate with one plain column, SQL still needs to know which single plain value should appear next to the summary.

Wrong: aggregate plus plain column

select product_name,
       count(*) as product_count
from products

Right: one count for the whole table

select count(*) as product_count
from products

Watch out

Exact rules vary. SQLite and MySQL without ONLY_FULL_GROUP_BY may choose a value from the group, while some engines accept columns they can prove are functionally dependent on the grouping key. Explicitly grouping each non-aggregate expression is the clearest portable interview rule.

How other engines word it

Engine	Message
DuckDB	`Binder Error: column "product_name" must appear in the GROUP BY clause or must be part of an aggregate function.`
PostgreSQL	`column "products.product_name" must appear in the GROUP BY clause or be used in an aggregate function`
SQL Server	`Column 'products.product_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.`
SQL Server	`Each GROUP BY expression must contain at least one column that is not an outer reference.` Appears instead when the grouped expression only references a column from an enclosing query.
MySQL	`Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'products.product_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` Only with ONLY_FULL_GROUP_BY enabled, which is the default from MySQL 5.7.5.