Module 3 computed one AVG(salary) for the entire employees table. The AVG() lesson's closing note flagged the natural next question: what's the average salary per department, not company-wide? GROUP BY is exactly that tool, it collapses rows sharing a value into groups, so an aggregate function runs once per group instead of once over everything.
Learning Objectives
- Use GROUP BY to compute an aggregate value per group instead of one overall value.
- Group by more than one column at once.
- Combine GROUP BY with a JOIN to group by a related table's column.
All Rows
Every employee
GROUP BY department_id
Collapsed into groups
AVG(salary)
Computed once per group
One Row Per Group
One average per department
Basic GROUP BY
Every row sharing the same department_id collapses into a single group, and AVG(salary) computes separately within each one, one row per distinct department_id in the result instead of one row for the entire table. Any column listed in SELECT that isn't inside an aggregate function must appear in GROUP BY too, department_id here, that's the rule the aggregate-functions-overview lesson in Module 3 originally flagged.
SELECT department_id, AVG(salary) AS average_salary FROM employees GROUP BY department_id;
Grouping By a Joined Column
Now that joins are covered, the department's actual name can be grouped by directly, instead of showing a raw department_id number. The join happens first, producing combined rows, then GROUP BY collapses those combined rows by d.name exactly the same way it collapsed by department_id above.
SELECT d.name AS department_name, AVG(e.salary) AS average_salary FROM employees e JOIN departments d ON e.department_id = d.id GROUP BY d.name;
Grouping By Multiple Columns
Listing two columns in GROUP BY creates one group per unique combination of both, headcount per department per hire year here, instead of one overall count per department. EXTRACT(YEAR FROM ...) is covered properly in the date functions module later in this course, read it here as just another expression that can be grouped by.
SELECT department_id, EXTRACT(YEAR FROM hire_date) AS hire_year, COUNT(*) AS headcount FROM employees GROUP BY department_id, EXTRACT(YEAR FROM hire_date);
Try It Yourself
Write a query returning each department's name alongside how many employees it has, using a join.
SELECT d.name AS department_name, COUNT(e.id) AS headcount FROM departments d LEFT JOIN employees e ON e.department_id = d.id GROUP BY d.name;
LEFT JOIN here, not a plain JOIN, so Legal still shows up with a headcount of 0 instead of disappearing entirely, exactly the pattern from the LEFT JOIN lesson.
Frequently Asked Questions
Can a query GROUP BY a column it doesn't also SELECT?
Yes, grouping by a column doesn't require selecting it, for example grouping by department_id purely to compute per-department averages, without needing department_id itself in the output.
Does GROUP BY run before or after WHERE?
After. WHERE filters individual rows first, then GROUP BY collapses whatever rows are left into groups. Filtering on a group's aggregate result (like "only departments averaging over 90000") needs HAVING instead, covered next.
Common Beginner Mistakes
Selecting a plain column that isn't in GROUP BY or wrapped in an aggregate
SELECT first_name, department_id, AVG(salary) FROM employees GROUP BY department_id fails (or, on some databases, silently picks an arbitrary first_name), first_name has one value per row but the query has already collapsed many rows into one per department, so SQL has no single first_name to show. Every selected column must either be in GROUP BY or inside an aggregate function.
Expecting GROUP BY department alone to also sort the output
GROUP BY only groups, it doesn't guarantee a sort order for the groups themselves. Add an explicit ORDER BY after GROUP BY if the groups need to appear in a specific order.
Best Practices
- Every non-aggregated column in SELECT must appear in GROUP BY, check this before running the query, not after a confusing error.
- Group by a joined table's descriptive column (like department name) once joins are available, it reads far more clearly than grouping by a raw foreign key.
- Add an explicit ORDER BY after GROUP BY whenever the groups' order in the output actually matters.
Interview Questions
Why does SELECT first_name, department_id, AVG(salary) FROM employees GROUP BY department_id fail in most databases?
GROUP BY collapses many rows into one per department_id, but first_name has a different value on every one of those original rows. With many rows now collapsed into one, there's no single first_name left to return for that group, every non-aggregated selected column must be part of GROUP BY, which first_name isn't here.
Write a query that returns each department's name and its average salary, including departments with zero employees.
SELECT d.name AS department_name, AVG(e.salary) AS average_salary FROM departments d LEFT JOIN employees e ON e.department_id = d.id GROUP BY d.name; — LEFT JOIN keeps Legal in the result even with zero employees, AVG() over zero rows (or all-NULL salary values) correctly returns NULL rather than 0 for it.
Summary
GROUP BY collapses rows sharing a value, or a combination of values, into groups, so an aggregate function computes separately per group instead of once over the whole table. Every non-aggregated SELECT column must appear in GROUP BY, and it combines cleanly with JOIN to group by a related table's own columns.
What's Next?
HAVING closes out this module, filtering groups by their aggregate result, the thing WHERE can't do, since WHERE only sees individual rows before grouping happens.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.