Why does SELECT with GROUP BY fail when you pick a column that is not grouped?
After grouping, each group becomes one output row, so a column that is neither in GROUP BY nor inside an aggregate has many possible values and the database cannot pick one. Group by it or aggregate it.
Explain it simply
Suppose you group students by department and then ask, "show the department and the student name". Computer Science has 60 students, so which one name goes in the single CS row? The database refuses to guess.
Example
SELECT dept, name, COUNT(*) -- error in most databases
FROM students
GROUP BY dept;Fix by aggregating:
SELECT dept, COUNT(*) AS total, MAX(name) AS last_name_alphabetically
FROM students
GROUP BY dept;Or by grouping on both columns:
SELECT dept, name, COUNT(*)
FROM students
GROUP BY dept, name;Older MySQL, with ONLY_FULL_GROUP_BY turned off, would silently return an arbitrary name. Since MySQL 5.7 the strict mode is on by default. PostgreSQL also allows columns that are functionally dependent on a grouped primary key.
What to say in the interview
"Every column in SELECT must either be in GROUP BY or wrapped in an aggregate, because one output row represents many input rows. If I need a detail row, I use a window function or join back to the grouped result."
Common mistakes
- Turning off strict mode to make the error go away instead of fixing the query.