Skip to content
Quantoros

What is the rule for columns in SELECT with GROUP BY?

SQLLevel: IntermediateAggregations & GROUP BY

What is the rule for columns in SELECT with GROUP BY?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

Every column in SELECT that is not inside an aggregate function must appear in the GROUP BY clause. Otherwise, the database doesn't know which value to return for that column (since multiple rows are being collapsed into one).

-- Valid: department is in GROUP BY, COUNT(*) is an aggregate
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

-- Invalid in standard SQL: name is not in GROUP BY or an aggregate
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

Note: MySQL historically allowed this and would return an arbitrary value for name. PostgreSQL and most other databases will throw an error.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions