Schema and sample data
| emp_id | name | department |
|---|---|---|
| 1 | Ava Torres | Engineering |
| 2 | Ben Ncube | Sales |
| 3 | Cara Singh | Engineering |
| 4 | Deng Liu | Support |
| 5 | Emma Rossi | Sales |
| 6 | Farid Khan | Engineering |
| 7 | Grace Lee | Sales |
| 8 | Hugo Marsh | Engineering |
Show setup SQL (copy to run)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(30) NOT NULL
);
INSERT INTO Employees (emp_id, name, department) VALUES
(1, 'Ava Torres', 'Engineering'),
(2, 'Ben Ncube', 'Sales'),
(3, 'Cara Singh', 'Engineering'),
(4, 'Deng Liu', 'Support'),
(5, 'Emma Rossi', 'Sales'),
(6, 'Farid Khan', 'Engineering'),
(7, 'Grace Lee', 'Sales'),
(8, 'Hugo Marsh', 'Engineering');From the Employees table, return each department together with its headcount, but only for departments that have more than 2 employees. Alias the count as headcount and sort the result by headcount from highest to lowest.
Expected result
| department | headcount |
|---|---|
| Engineering | 4 |
| Sales | 3 |
Show hint
Collapse the rows into one group per department with GROUP BY, then count the rows in each group. You cannot filter a group with WHERE because it runs before the grouping happens - reach for HAVING to keep only the groups whose count clears the threshold.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT department, COUNT(*) AS headcount
FROM Employees
GROUP BY department
HAVING COUNT(*) > 2
ORDER BY headcount DESC;GROUP BY department folds the eight rows into one row per department: Engineering (4 rows), Sales (3 rows) and Support (1 row). COUNT(*) then counts the rows inside each group, and aliasing it as headcount gives the output column its name.
The key idea is the difference between WHERE and HAVING. WHERE filters individual rows before they are grouped, so it can only see column values like department, not an aggregate. HAVING filters whole groups after the aggregation, so it can test COUNT(*) > 2. That is why Support, with a single employee, is dropped by HAVING while Engineering and Sales survive. For a deeper walkthrough see WHERE vs HAVING.
A common mistake is trying to write WHERE COUNT(*) > 2, which fails because the count does not exist yet at the WHERE stage. Another is putting HAVING COUNT(*) > 2 but forgetting the GROUP BY, which collapses everything into a single group. Finally, ORDER BY headcount DESC sorts the two surviving departments so the largest headcount appears first.