Why does SELECT department, name, COUNT(*) FROM employees GROUP BY department fail?
answer
- Grouping produces one row per group
- Which single name would the group print?
- Every column: grouped or inside an aggregate
- GROUP BY department, name — or MAX(name)
basics
~20 sGROUP BY department collapses every department into one output row, and that row has many different name values, so name is ambiguous. Standard SQL requires each select-list column to be a grouping column or sit inside an aggregate.
solid answer
~50 s`GROUP BY department` folds all rows of a department into a **single output row**. `COUNT(*)` is defined over that whole group, but `name` is not: the group holds many names, and the engine has no rule for choosing one, so the standard rejects the query with an error like *"column employees.name must appear in the GROUP BY clause or be used in an aggregate function"*. The rule is simple: in a grouped query, every select-list item must either be a grouping expression or be wrapped in an aggregate. There are three honest fixes — add `name` to `GROUP BY` (which changes the granularity to one row per department-and-name), wrap it in an aggregate such as `MAX(name)`, or drop it from the select list. The same restriction applies to `HAVING` and `ORDER BY` in that query.
go deeper
Be ready to state the rule from memory and fix a broken query on the spot: every select-list column is either in GROUP BY or inside an aggregate.
Explain why the rule follows from one-output-row-per-group, and show that adding the column to GROUP BY changes the result's granularity rather than merely silencing the error.
Show judgment about which of the three fixes matches the report's intent, and flag queries that pass only because an engine's permissive mode returns an unspecified value.
Frame it as determinism policy: queries whose output depends on an engine's tolerance for ungrouped columns are unportable and untestable, so enforce the strict mode across all environments.
## The error you are looking at Run this against any standards-conforming engine: ```sql SELECT department, name, COUNT(*) FROM employees GROUP BY department; ``` and you get a parse/analysis error along the lines of *"column \"employees.name\" must appear in the GROUP BY clause or be used in an aggregate function"*. The wording differs per engine, but the cause is always the same. ## Why the rule exists: one row per group `GROUP BY department` partitions the rows of `employees` into disjoint groups, one per distinct department value, and the query then produces **exactly one output row per group**. Everything in the select list must therefore be a single value *per group*. - `department` qualifies: by construction every row in the group has the same department, so there is one value to print. - `COUNT(*)` qualifies: an aggregate function is defined as a mapping from the whole group to one value. - `name` does not qualify: the Engineering group may hold 40 rows with 40 different names. Printing "the name" of that group is not a well-defined operation, and SQL refuses to guess. So the rule — every selected column is either grouped or aggregated — is not an arbitrary restriction. It is the condition under which the select list is even meaningful. ## What the standard says SQL-92 stated it plainly: in a query with `GROUP BY`, a column reference outside an aggregate must be one of the grouping columns. SQL:1999 relaxed it slightly for columns that are *functionally dependent* on the grouping columns (for example, grouping by a table's primary key). That relaxation is narrow and unevenly implemented; the working rule remains "group it or aggregate it". The restriction is not limited to the select list. In the same grouped query, `HAVING` and `ORDER BY` also see only grouping expressions, aggregates, and select-list aliases — the ungrouped detail columns no longer exist at that stage. `WHERE`, which is evaluated **before** grouping, still sees every base column; that is precisely why row filters go in `WHERE` and group filters go in `HAVING`. ## The three fixes, and what each one means **1. Add the column to `GROUP BY`.** This is not a cosmetic edit — it changes the shape of the answer: ```sql SELECT department, name, COUNT(*) AS row_count FROM employees GROUP BY department, name; ``` You now get one row per *(department, name)* pair, and `COUNT(*)` counts rows for that pair, not for the department. If you wanted headcount per department, this is the wrong answer even though it compiles. **2. Aggregate the column.** Keep one row per department and reduce the extra column to a single value: ```sql SELECT department, COUNT(*) AS headcount, MAX(hire_date) AS latest_hire FROM employees GROUP BY department; ``` This is right when the aggregate is what you actually mean ("the latest hire date"). It is a trap when you use `MIN`/`MAX` merely to silence the error — each aggregate is computed independently, so values from different columns can come from different rows. **3. Drop the column.** Often the honest fix: a per-department summary simply has no room for a per-employee name. ## Engines that do not enforce it Some engines historically accepted the bare column and returned an unspecified value from the group. MySQL did so until `ONLY_FULL_GROUP_BY` became part of the default SQL mode in 5.7, and it offers `ANY_VALUE(col)` to say "any value from the group, I accept the indeterminacy" explicitly. SQLite also permits bare columns. If your only SQL experience is on a permissive configuration, the first error on a stricter engine is a surprise — which is exactly why interviewers ask this. ## How to talk about it Say what the group is (one output row per distinct grouping value), say why an ungrouped column has no single value there, name the three fixes, and note that adding the column to `GROUP BY` changes the result granularity rather than just appeasing the parser. That answer separates someone who understands grouping from someone who has memorized an error message.
- Does adding the column to GROUP BY always give the same answer as aggregating it?No — it changes the granularity. `GROUP BY department, name` produces one row per department-and-name pair, so `COUNT(*)` counts that pair's rows rather than the whole department. Aggregating instead (`MAX(name)`) keeps one row per department. Choose by what the report is supposed to mean, not by which one stops the error.
- Does the same restriction apply to ORDER BY and HAVING in a grouped query?Yes. Once grouping has happened, `HAVING` and `ORDER BY` can reference only grouping expressions, aggregates, or select-list aliases; the ungrouped detail columns are gone. `WHERE` is different — it runs before grouping, so it still sees every base column, which is why row-level filters belong there.
- Why do some engines accept the bare column without complaining?They relax the check and return an unspecified value from the group. MySQL behaved that way until `ONLY_FULL_GROUP_BY` entered the default SQL mode in 5.7, and SQLite permits it too. The value is not guaranteed to be stable across runs or plans, so the query is non-deterministic even where it executes.
saying these in an interview costs you the question
- Claims the engine just picks the first row's value
- Says GROUP BY is optional when COUNT(*) is present
- Adds the column to GROUP BY without noticing granularity changed
- Thinks WHERE and HAVING have the same visibility of columns
- Believes the rule is a quirk of one specific database