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

Loading playground environment...

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:

Loading playground environment...

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.

Related examples

GROUP BY Basics: Aggregating Rows Into Summaries

group by

Collapse individual orders into one summary row per customer using GROUP BY, COUNT, and SUM.

INNER JOIN: Combining Rows From Two Related Tables

join

Use INNER JOIN to attach department names to employee records, keeping only rows that match on both sides.

Three-Table JOIN: Customers, Orders, and Products

join

Chain INNER JOINs across the store schema to show what each customer bought and for how much.

Cite this resource

SQLSimplified. "JOIN Followed by GROUP BY Example". Available at: https://sqlsimplified.online/examples/join-with-group-by