On-Payroll Headcount by Department and Work Mode
The facilities team is planning desks and needs to know how each department works.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #224
Problem statement
The facilities team is planning desks and needs to know how each department works. Everyone on the payroll counts — that is status 'active' and also 'notice' (people serving their notice period still come in); 'exited' employees do not.
Return one row per department that has at least one on-payroll employee, with the columns department, onsite, hybrid, remote (the number of on-payroll employees in each work mode, 0 when none) and total. Return the rows in any order.
Tables
Table: Employee
| Column | Type |
|---|---|
| emp_id | int |
| department | varchar |
| work_mode | enum(onsite, hybrid, remote) |
| status | enum(active, notice, exited) |
Primary key: emp_id.
One row per employee, past or present. work_mode is the arrangement in the employee's contract.
Examples
Example 1
Employee
| emp_id | department | work_mode | status |
|---|---|---|---|
| 1 | Engineering | hybrid | active |
| 2 | Engineering | remote | active |
| 3 | Engineering | hybrid | notice |
| 4 | Sales | onsite | active |
| 5 | Sales | onsite | exited |
| 6 | Legal | remote | exited |
| 7 | Support | onsite | active |
| 8 | Support | remote | notice |
Output
| department | onsite | hybrid | remote | total |
|---|---|---|---|---|
| Engineering | 0 | 2 | 1 | 3 |
| Sales | 1 | 0 | 0 | 1 |
| Support | 1 | 0 | 1 | 2 |
How to solve On-Payroll Headcount by Department and Work Mode
The pattern is conditional aggregation: one group per department, several counts per group, each counting only the rows that meet its own condition.
Filter first: WHERE status IN ('active', 'notice') keeps everyone on the payroll. Because the filter runs before grouping, a department whose employees have all exited produces no group at all, which is what the statement asks for. Then GROUP BY department, and inside each group SUM(CASE WHEN work_mode = 'onsite' THEN 1 ELSE 0 END) adds 1 for each onsite row and 0 for the rest — so a department with nobody remote gets a 0, not a NULL. COUNT(*) is the total.
SUM(IF(…, 1, 0)) is MySQL shorthand for the same thing, and COUNT(CASE WHEN … THEN 1 END) works because a CASE without ELSE yields NULL, and COUNT skips NULLs. All three read the table once — it is a single pass with a hash on department.
Reference solution (MySQL)
SELECT department,
SUM(CASE WHEN work_mode = 'onsite' THEN 1 ELSE 0 END) AS onsite,
SUM(CASE WHEN work_mode = 'hybrid' THEN 1 ELSE 0 END) AS hybrid,
SUM(CASE WHEN work_mode = 'remote' THEN 1 ELSE 0 END) AS remote,
COUNT(*) AS total
FROM Employee
WHERE status IN ('active', 'notice')
GROUP BY departmentAnother way
SELECT department, SUM(IF(work_mode = 'onsite', 1, 0)) AS onsite, SUM(IF(work_mode = 'hybrid', 1, 0)) AS hybrid,
SUM(IF(work_mode = 'remote', 1, 0)) AS remote, COUNT(*) AS total
FROM Employee WHERE status <> 'exited' GROUP BY departmentAnother way
SELECT department, COUNT(CASE WHEN work_mode = 'onsite' THEN 1 END) AS onsite,
COUNT(CASE WHEN work_mode = 'hybrid' THEN 1 END) AS hybrid,
COUNT(CASE WHEN work_mode = 'remote' THEN 1 END) AS remote, COUNT(emp_id) AS total
FROM Employee WHERE status IN ('active', 'notice') GROUP BY department← Employees Overdue for Probation Confirmation · Employees Without a Reachable Emergency Contact →