SQL error

Fix “Column Must Appear in the GROUP BY Clause” in SQL

Every SELECT column that is not inside an aggregate has to be grouped or aggregated itself.

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. For every column you SELECT, the database needs to know which single value to show for the group. If a column is neither part of the grouping key nor wrapped in an aggregate, there is no single answer, so it refuses.

The fix

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

Why it can happen with no GROUP BY clause

You can see the same error even when your query has no GROUP BYat 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

SQLite and loosely configured MySQL databases may let this slide and pick an arbitrary value instead of erroring. That is worse because you get a silent wrong answer. Group explicitly and you are safe everywhere.

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 (`ONLY_FULL_GROUP_BY`)	`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`
SQLite	No error. It picks an arbitrary row's value for the ungrouped column.