join

JOIN Followed by GROUP BY

Join employees to departments, then aggregate to compute headcount and average salary per department.

Overview

Joins and aggregates compose naturally: join first to bring related columns into the same row, then GROUP BY a column from either table to roll up. Here we join employees to departments and group by department to report headcount and average pay per team.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

INNER JOIN departments d — attach each employee's department name.
GROUP BY d.name — one output row per department.
COUNT(e.id) counts employees; AVG(e.salary) averages their pay.
ROUND(..., 2) tidies the average to two decimals.

What you can SELECT after GROUP BY

You may only reference grouped columns or aggregate functions in the SELECT list — e.first_name would be illegal here because it's neither grouped nor aggregated.

Variations

Group by department location instead:

Employees & Departments

Loading database engine...

Common Mistakes

Selecting non-grouped columns. SELECT e.first_name, d.name … GROUP BY d.name errors because first_name isn't aggregated.
Grouping by the wrong column. Grouping by d.id vs d.name gives the same rows here, but you can only SELECT the grouped expression — pick the one you actually want to display.
Forgetting the join. Grouping employees alone can't show department names; you need the join first.