GROUP BY Mistakes That Break SQL Queries

All examples use an employees(name, department, salary, active) table, with an orders table where noted.

1. Selecting a column that isn't grouped or aggregated

This is the error everyone meets first: "column 'name' must appear in the GROUP BY clause or be used in an aggregate function." Each group produces one row, but name has many values per department — so the database can't pick one.

-- ❌ name is neither grouped nor aggregated
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

-- ✅ Fix A: only select grouped + aggregated columns
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

-- ✅ Fix B: group at a finer level if you truly want name
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department, name;

2. Filtering aggregates with WHERE instead of HAVING

WHERE runs before grouping, so aggregates don't exist yet. Conditions on COUNT(), SUM(), etc. belong in HAVING.

-- ❌ Aggregate in WHERE → error
SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 5
GROUP BY department;

-- ✅ Aggregate condition goes in HAVING
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Full breakdown in SELECT vs WHERE vs HAVING.

3. Assuming GROUP BY sorts the results

GROUP BY collapses rows into groups but makes no promise about order. Databases often return groups in a convenient-looking order, then change it after an index or version change — and your "sorted" report silently breaks. Always add ORDER BY.

-- ✅ Be explicit about ordering
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
ORDER BY headcount DESC;

4. Using SELECT * with GROUP BY

SELECT * pulls every column, but a grouped query can only return grouped or aggregated columns. In standard SQL this errors; in loose MySQL it returns arbitrary values. List the columns you actually mean.

-- ❌ Ambiguous: which row's columns win per group?
SELECT * FROM employees GROUP BY department;

-- ✅ Say exactly what each group should show
SELECT department,
       COUNT(*)      AS headcount,
       AVG(salary)  AS avg_salary
FROM employees
GROUP BY department;

5. Join fan-out: inflated SUM and COUNT

The nastiest bug, because it doesn't error — it just returns wrong numbers. When you join a one-to-many relationship, each parent row is duplicated per child, so aggregates over the parent are multiplied.

-- ❌ If each order has several items, order.total is counted once PER item
SELECT o.customer_id, SUM(o.total) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.customer_id;

-- ✅ Aggregate at the right grain first, then join
SELECT customer_id, SUM(total) AS revenue
FROM orders
GROUP BY customer_id;

6. COUNT(column) silently skipping NULLs

COUNT(*) counts rows; COUNT(column) counts only rows where that column is not NULL. Mixing them up quietly under-counts.

-- These are NOT the same if phone has NULLs
SELECT
  COUNT(*)      AS all_rows,      -- every row
  COUNT(phone) AS has_phone      -- only non-NULL phones
FROM employees;

Use whichever you actually mean — this deserves its own deep dive (coming soon in "COUNT(*) vs COUNT(column)").

7. Sorting month names alphabetically

Grouping by a month name and sorting by it gives you April, August, December… — alphabetical, not calendar order. Sort by the month number.

-- ✅ Group/label by name, but sort by month number
SELECT MONTHNAME(order_date) AS month, COUNT(*) AS orders
FROM orders
GROUP BY MONTH(order_date), MONTHNAME(order_date)
ORDER BY MONTH(order_date);

Mistake → fix cheat sheet