From one summary to one per group
The previous lesson turned a whole column into a single number: one average, one count. That is a fine answer to "how many people work here?" — but it is useless for "how many people work in each department?", which needs five answers, one per department.
You could run five separate queries, swapping the department each time. Nobody does, because the moment a sixth department appears you have to find and update every one of them. GROUP BY does it in one pass: it sorts the rows into one bucket per distinct value, then runs the aggregate inside each bucket. The result is one row per group.
flowchart TB E[employees] -->|"GROUP BY d.name"| B1[Engineering] E --> B2[Marketing] E --> B3[Sales] E --> B4[Support] E --> B5[Research] B1 -->|"COUNT"| C1[4] B2 -->|"COUNT"| C2[2] B3 -->|"COUNT"| C3[3] B4 -->|"COUNT"| C4[3] B5 -->|"COUNT"| C5[2] style B1 fill:#0e7490,color:#fff style B2 fill:#0e7490,color:#fff style B3 fill:#0e7490,color:#fff style B4 fill:#0e7490,color:#fff style B5 fill:#0e7490,color:#fff
What you may put in SELECT
GROUP BY d.name creates one bucket for each department name. Inside each bucket, aggregates like COUNT and AVG collapse that department's rows into the numbers you asked for.
There is a rule that follows from this: every bare column in your SELECT must also appear in the GROUP BY, because the only column the database can name for a whole group is the one the group is built on. SELECT d.name, COUNT(e.id) is correct — name is the grouping column and COUNT is an aggregate. SELECT d.name, e.salary next to a GROUP BY is meaningless: which of the department's several salaries would it print?
Mix a grouping column with one or more aggregates, and that is the whole shape of a grouped query.
SELECT d.name, COUNT(e.id) AS headcount, AVG(e.salary) AS avg_salary FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name;
WHERE versus HAVING
These two clauses both filter, and mixing them up is the most common GROUP BY mistake. The difference is when they run.
WHERE filters rows before they are grouped. Use it to throw out individual rows you never want in a bucket — for example, exclude contractors before counting heads.
HAVING filters groups after the aggregate is calculated. Use it to keep only buckets that meet a condition on the summary itself — for example, only departments whose average salary tops 600000.
You cannot put an aggregate inside WHERE, because at the moment WHERE runs the aggregate has not been computed yet. HAVING exists precisely for that job.
SELECT d.name, COUNT(e.id) AS headcount FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name HAVING COUNT(e.id) >= 3;
GROUP BY does not sort the output
A common surprise: GROUP BY groups rows but does not promise any particular order for those groups. The departments may come back in whatever sequence the engine finds convenient. If you want the busiest department first, or departments alphabetically, add an explicit ORDER BY.
ORDER BY COUNT(e.id) DESC sorts the groups by their computed headcount; ORDER BY d.name sorts them alphabetically. Treat GROUP BY as "make the buckets," and ORDER BY as the separate step that arranges them.
flowchart LR F["FROM + JOIN"] --> W["WHERE<br/>filter rows"] W --> G["GROUP BY<br/>build buckets"] G --> H["HAVING<br/>filter groups"] H --> S["SELECT<br/>columns + aggregates"] S --> O["ORDER BY"] style W fill:#0e7490,color:#fff style H fill:#b45309,color:#fff
Grouping by more than one column
You can group by several columns at once, and the database makes one bucket for every distinct combination. Group employees by both department and role, and Engineering's Engineers form one bucket while Engineering's Leads form another.
Each extra grouping column refines the buckets further, so the rows multiply. That is useful when a single category is too coarse — average salary per department hides the gap between a Lead and a junior; average per department-and-role exposes it. Add grouping columns until the buckets answer the question you actually have, and no further.
GROUP BY alone behaves like DISTINCT
If you write GROUP BY with no aggregate at all, it simply collapses duplicate values — the same job SELECT DISTINCT does. SELECT role FROM employees GROUP BY role lists each role once. That is legal, but when you are not summarising, DISTINCT states your intent more clearly. Reach for GROUP BY when there is an aggregate to compute; reach for DISTINCT when you only want the unique values.
For each department, return its name and the number of employees as headcount. Group by the department name.
SELECT d.name, COUNT(e.id) AS headcount FROM departments d JOIN employees e ON d.id = e.department_id -- finish the query ;
Use
GROUP BY d.nameto create one bucket per department.Count employees with
COUNT(e.id).
SELECT d.name, COUNT(e.id) AS headcount FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name;
Return the name of each department whose average salary is greater than 600000, along with that average as avg_salary. Use HAVING.
SELECT d.name, AVG(e.salary) AS avg_salary FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name -- filter groups here ;
Group by department first, then compute the average.
Use
HAVING AVG(e.salary) > 600000to keep only high-paying departments.
SELECT d.name, AVG(e.salary) AS avg_salary FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name HAVING AVG(e.salary) > 600000;
This query is meant to list only departments with three or more employees, but it fails — the aggregate condition sits in WHERE, where aggregates are not allowed. Move it to the clause that runs after grouping.
SELECT d.name, COUNT(e.id) AS headcount FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name WHERE COUNT(e.id) >= 3 ;
Aggregates like
COUNTcannot appear in WHERE.Replace
WHEREwithHAVING, which filters groups after they are built.
SELECT d.name, COUNT(e.id) AS headcount FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name HAVING COUNT(e.id) >= 3;
For each department, return its name and the salary_range — the gap between the highest and lowest salary in that department, as MAX(salary) - MIN(salary). Group by department.
SELECT d.name, -- combine two aggregates into one expression FROM departments d JOIN employees e ON d.id = e.department_id -- group here ;
You need both
MAX(e.salary)andMIN(e.salary).Subtract one from the other, alias as
salary_range, and group byd.name.
SELECT d.name, MAX(e.salary) - MIN(e.salary) AS salary_range FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name;
Recap
GROUP BYmakes one bucket per distinct value and runs aggregates inside each.- A grouped
SELECTnames the grouping column plus aggregates — nothing else. WHEREfilters rows before grouping;HAVINGfilters groups after.- Aggregates in
WHEREare an error — move them toHAVING. - Group by several columns to make finer buckets per combination.
Next you'll link tables together with joins, so a query can reach across departments, people, and projects at once.