'Column Must Appear in the GROUP BY Clause' — Why SQL Won't Let You Do That

It isn't a syntax error and it isn't arbitrary. SQL is asking a question it genuinely can't answer on its own — and once you see that, the fix is obvious.

Adapted from the SQL Essentials Companion Guide.

---

You add one more column to a GROUP BY query, run it, and get this instead of results:

ERROR: column "products.name" must appear in the GROUP BY clause
or be used in an aggregate function

The query looked reasonable. Nothing is misspelled. But SQL is refusing to run it at all — not returning wrong data, just flatly declining. That refusal is the whole story: SQL isn't confused about syntax, it's telling you the question you asked doesn't have a single correct answer.

What's actually happening

SELECT category, name, COUNT(*) FROM products
GROUP BY category;

GROUP BY category collapses every row into one row per category. That's the point of grouping — dozens of individual product rows become a handful of category rows. But name is a per-row value, and each category groups together many different products, each with its own name. Once those rows are collapsed into one, which product's name is supposed to show up in the result?

There's no good answer, and SQL doesn't guess. Every column in the SELECT list has to be something that still makes sense after the collapse: either a column named in GROUP BY (one value per group, by definition), or the output of an aggregate function like COUNT(), SUM(), or MAX() (a value computed from the whole group, so it's well-defined no matter how many rows are in it). name is neither — it's a leftover from before the grouping happened, and the database won't silently pick one at random on your behalf.

The fix, step by step

1. Decide what you actually want name to mean in a grouped result. There are two real answers, and the fix depends on which one you meant:

2. If you wanted one row per product, not per category — you probably don't want to group by category at all, or you need category and name to define the group together:
   

   SELECT category, name, COUNT(*) FROM products
   GROUP BY category, name;

Now each group is one specific product within one category, so name has exactly one value per group.

1. If you genuinely only want one row per category, drop the column that doesn't belong at that level:

   SELECT category, COUNT(*) FROM products
   GROUP BY category;

1. If you wanted some representative name, not every name, wrap it in an aggregate that resolves the ambiguity on purpose — MIN(name), MAX(name), or, on databases that support it, STRING_AGG(name, ', ') to list all of them:

   SELECT category, STRING_AGG(name, ', ') AS product_names, COUNT(*)
   FROM products
   GROUP BY category;

Two mistakes worth knowing about ahead of time

Reaching for MAX() or MIN() just to make the error disappear. It works — the query runs — but if you didn't actually mean "the alphabetically last name," you've just replaced a clear error with a quietly wrong result. MAX()/MIN() are the right tool when you deliberately want a representative value, not a reflexive fix for an error you haven't read.

Assuming this error means the query is broken. It's the opposite: this is one of the few SQL errors that catches a real logical mistake before it produces bad data instead of after. A query that ran without complaint and silently picked one arbitrary name per group (which is what some databases, like older MySQL configurations, actually did before enforcing this rule) is far more dangerous than one that stops and asks you to clarify.

A habit that prevents the confusion entirely

Before adding any column to a GROUP BY query's SELECT list, ask what it's supposed to represent once rows collapse into groups: one value per group (put it in GROUP BY), a computed summary across the group (wrap it in an aggregate), or something else — in which case it probably means you're grouping by the wrong thing, or trying to answer two different questions in one query. The error isn't the database being pedantic. It's the one moment SQL forces you to answer a question you'd otherwise skip past.

---

If you'd like more posts like this sent straight to your inbox, subscribe to the newsletter.

Prefer to dig in yourself? The SQL Essentials repo on GitHub has more free examples and exercises.