Course contentsShow
← Data Science
Lesson 897 of 2,14523. SQL Aggregation and GroupingFree lesson

Common GROUP BY Mistakes

Avoiding errors like selecting non-aggregated columns not in GROUP BY and misunderstanding aggregate scope.

Common GROUP BY Mistakes

You'll learn to avoid the two most frequent errors that trip up beginners when grouping data in SQL.

The Non-Aggregated Column Error

The most common mistake is selecting columns that aren't in your GROUP BY clause and aren't wrapped in an aggregate function. Think of it like organizing students by grade level: if you group by grade, you can't just pick one random student's name to represent the whole grade—you need to say "count of students" or "average age" instead.

The Rule: Every column in your SELECT must either:

  1. Appear in the GROUP BY clause, OR
  2. Be inside an aggregate function (COUNT, SUM, AVG, etc.)
-- ❌ WRONG: city not in GROUP BY or aggregate
SELECT country, city, COUNT(*)
FROM customers
GROUP BY country;

-- ✅ CORRECT: only country and aggregates
SELECT country, COUNT(*), MAX(city)
FROM customers
GROUP BY country;

Misunderstanding Aggregate Scope

Another mistake is forgetting that aggregate functions collapse all rows in each group into one value. You can't mix row-level and group-level thinking.

-- ❌ WRONG: WHERE can't filter aggregates
SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 50000  -- AVG doesn't exist yet!
GROUP BY department;

-- ✅ CORRECT: use HAVING after GROUP BY
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Remember: WHERE filters individual rows before grouping; HAVING filters groups after aggregation.

Key Takeaway: In GROUP BY queries, every selected column must be either grouped or aggregated—never select ungrouped, non-aggregated columns, and use HAVING (not WHERE) to filter aggregate results.