Practical SQL Guide · MySQL 8.0

Common GROUP BY errors and debugging

Use result grain to catch missing and excessive grouping, including MySQL ONLY_FULL_GROUP_BY errors.

Problem and reasoning

GROUP BY is primarily a result-grain decision, not a syntax pattern. Describe one output row in a sentence, such as “one department's total amount.” Dimensions in that sentence belong in GROUP BY, while measures use aggregates such as SUM.

Under-grouping selects a regular column that is neither aggregated nor grouped. MySQL 8 normally enables ONLY_FULL_GROUP_BY and rejects this ambiguous query. A permissive mode may execute it, but the ordinary column can be an arbitrary value from the group and must not be trusted.

Over-grouping is just as common. If the target is a department total but employee name is also grouped, each department splits into several rows. The SQL succeeds, yet its grain has changed from department to department-employee, which can be harder to notice than an error.

Debug with grouping keys and COUNT(*) first. Verify the number of groups and detail rows per group, then add SUM, AVG, or conditional aggregates one at a time. If adding a regular column changes row count, decide whether it is truly a dimension, a measure, or only a display attribute.

Omit functionally dependent fields only when the database can reliably prove the dependency. Teaching examples and portable queries should list grouping columns explicitly instead of depending on a server SQL mode.

Examples

Sample table

Table：orders

orders
name	department	amount
Alice	Sales	1200
Bob	IT	900
Cindy	Sales	1500
David	HR	700

Before / Not recommended

SELECT department, name, SUM(amount) AS total FROM orders GROUP BY department, name ORDER BY department, name;

Before result

result
department	name	total
HR	David	700
IT	Bob	900
Sales	Alice	1200
Sales	Cindy	1500

After / Recommended

SELECT department, SUM(amount) AS total FROM orders GROUP BY department ORDER BY department;

After result

result
department	total
HR	700
IT	900
Sales	2700

Why it is better：Both statements run in MySQL 8, but grouping by name further splits departments. The second matches the intended one-row-per-department grain.