How to Count Employees by Department and Filter with HAVING in SQL?

Use GROUP BY and HAVING to find departments with more than 5 employees

Aggregating data by group is one of the most common things you'll do in SQL, and the question "which departments have more than 5 employees?" is a favorite in interviews and real-world reporting alike. It looks simple, but it's the perfect place to learn the difference between WHERE and HAVING. In this post, we'll build the query, explain how it works, and cover the details that often cause mistakes.

The problem

You have an employees table with a department_id column. You want to count the employees in each department and return only the departments that have more than 5 employees.

The solution

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

How it works

1. GROUP BY department_id places all employees of the same department into one group.
2. COUNT(*) counts the rows in each group, giving the headcount per department.
3. HAVING COUNT(*) > 5 keeps only the groups whose count is greater than 5.

Why HAVING and not WHERE?

This is the key idea. WHERE filters individual rows before grouping happens, so it has no access to aggregate results like COUNT(*). HAVING filters groups after aggregation, which is exactly what we need to filter on a count.

A handy way to remember the order of execution: FROM and WHERE first, then GROUP BY, then HAVING, and finally SELECT and ORDER BY.

Things to watch for

Strictly greater than

The condition > 5 excludes departments with exactly 5 employees. If you want "5 or more," use >= 5. Read the requirement carefully, because this off-by-one detail is a common trap.

NULL departments

Employees whose department_id is NULL are grouped together as their own group. If you don't want that group in your results, filter them out first.

SELECT department_id, COUNT(*) AS employee_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING COUNT(*) > 5;

COUNT(*) versus COUNT(column)

COUNT(*) counts every row in the group, while COUNT(column) skips rows where that column is NULL. For a headcount, COUNT(*) is the right choice unless you have a specific reason to ignore missing values.

Empty departments never appear

A department with zero employees has no rows to group, so it will not show up in the results. That is fine here, since zero is never greater than 5, but keep it in mind if you later need a report that lists every department.

Showing department names

In most databases, department names live in a separate departments table. Join it before grouping to get readable output.

SELECT d.name AS department, COUNT(*) AS employee_count
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.id, d.name
HAVING COUNT(*) > 5
ORDER BY employee_count DESC;

What changed?

• The join brings in the department name for display.
• Grouping by both d.id and d.name prevents two different departments with the same name from being merged into one group. Many databases also require every non-aggregated selected column to appear in the GROUP BY.
• ORDER BY employee_count DESC is optional, but it lists the largest departments first, which makes the report easier to scan.

Which version should you use?

Goal	Best approach
Quick answer using IDs	The basic GROUP BY with HAVING query
Readable report with department names	Join to the departments table, then group
Ignore employees without a department	Add WHERE department_id IS NOT NULL

For interviews, start with the basic query, then mention the join and NULL handling to show that you think about real data.

Final thoughts

The pattern GROUP BY ... HAVING COUNT(*) > N is one of the most reusable tools in SQL. It answers questions like "which customers placed more than 3 orders?" or "which products have more than 10 reviews?" with only a change of table and column names. Remember that WHERE filters rows, HAVING filters groups, and you'll avoid one of the most common SQL mistakes.