Count Employees Per Department — SQL

Solving tips

Reach for GROUP BY the moment you see "per" or "for each" in the prompt.
COUNT(*) counts every row in the group, but COUNT(col) skips rows where col is NULL — pick deliberately.
Every non-aggregated column in SELECT must appear in GROUP BY.

Given an employee roster, report how many people work in each department and how many of them report to a manager.

Schema

CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  department TEXT NOT NULL,
  manager_id INTEGER   -- NULL for employees with no manager
);

Sample data:

id	name	department	manager_id
1	Alice	Engineering	NULL
2	Bob	Engineering	1
3	Carol	Engineering	1
4	Dave	Sales	NULL
5	Eve	Sales	4
6	Frank	Marketing	NULL

Task

For each department, return:

department
total_employees — the number of employees in the department
employees_with_manager — the number of employees in the department that have a non-NULL manager_id

Order the result by department ascending.

Expected output

department	total_employees	employees_with_manager
Engineering	3	2
Marketing	1	0
Sales	2	1

Approach

Group the rows by department and apply two different counters in the same SELECT. COUNT(*) gives the size of each group because it counts rows regardless of content, while COUNT(manager_id) counts only rows where manager_id is not NULL. Putting both in one query shows the difference between the two forms directly.

Query

SELECT
  department,
  COUNT(*)          AS total_employees,
  COUNT(manager_id) AS employees_with_manager
FROM employees
GROUP BY department
ORDER BY department ASC;

Walkthrough

Rows collapse into three groups: Engineering (ids 1, 2, 3), Sales (ids 4, 5), Marketing (id 6).
Engineering: COUNT(*) = 3; manager_id is NULL for Alice but set for Bob and Carol, so COUNT(manager_id) = 2.
Sales: COUNT(*) = 2; Dave is NULL and Eve is set, so COUNT(manager_id) = 1.
Marketing: COUNT(*) = 1; Frank’s manager_id is NULL, so COUNT(manager_id) = 0.
ORDER BY department sorts alphabetically: Engineering, Marketing, Sales.

Complexity & notes

Single scan of the table plus a hash or sort aggregate: effectively O(n) over the rows.
The core pitfall: COUNT(*) and COUNT(column) are not interchangeable. COUNT(column) ignores NULLs, which is exactly what gives employees_with_manager. Writing COUNT(*) there would wrongly return the full group size.
COUNT never returns NULL — a group with zero non-NULL values returns 0, as Marketing shows.
Every selected non-aggregate column (department) is in GROUP BY; Postgres and most engines require this.