A non-aggregated column sits beside COUNT(*) with a partial GROUP BY and returns nonsense — fix it
This query is meant to count employees per department but returns a wrong, arbitrary department name next to each count:
SELECT department_id, department_name, COUNT(*)
FROM employees
GROUP BY department_id;department_name is not in the GROUP BY. Explain what the engine does with it and fix the query.
department_name is neither grouped nor aggregated, which is invalid SQL. Strict engines reject it; MySQL in a loose mode silently returns an arbitrary row's value — the nonsense seen. Fix it by adding department_name to GROUP BY or wrapping it in MIN/MAX.
- ✗Assuming a functionally-dependent column is carried automatically
- ✗Blaming display or collation instead of the ungrouped-column rule
- ✗Thinking COUNT(*) itself is what makes the query invalid
- →Why do strict and loose SQL modes disagree on whether this even runs?
- →When is a functional-dependency exception to GROUP BY actually allowed?
The SELECT list has a column, department_name, that is neither in the GROUP BY nor wrapped in an aggregate. That violates the grouping rule: each output row represents a whole group, but department_name has one value per underlying row, so the engine has no single defined value to show.
Strict engines (PostgreSQL, or MySQL with ONLY_FULL_GROUP_BY) reject the query outright. MySQL in a permissive mode runs it and returns an arbitrary row's department_name from each group — which looks like nonsense.
Fix by making the grouping match the select list:
SELECT department_id, department_name, COUNT(*) AS n
FROM employees
GROUP BY department_id, department_name;Because department_name is functionally determined by department_id, adding it to GROUP BY does not change the number of groups. Alternatively, wrap it as MIN(department_name) if you must group by the id alone.
