On-Payroll Headcount by Department and Work Mode

Easy
224.

On-Payroll Headcount by Department and Work Mode

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

ColumnTypeValues
emp_idint
departmentvarchar
work_modeenumonsite, hybrid, remote
statusenumactive, notice, exited

Primary key: emp_id.

One row per employee, past or present. work_mode is the arrangement in the employee's contract.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Employee

emp_iddepartmentwork_modestatus
1Engineeringhybridactive
2Engineeringremoteactive
3Engineeringhybridnotice
4Salesonsiteactive
5Salesonsiteexited
6Legalremoteexited
7Supportonsiteactive
8Supportremotenotice

Output

departmentonsitehybridremotetotal
Engineering0213
Sales1001
Support1012
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
emp_idint
departmentvarchar
work_modeenum(onsite, hybrid, remote)
statusenum(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_iddepartmentwork_modestatus
1Engineeringhybridactive
2Engineeringremoteactive
3Engineeringhybridnotice
4Salesonsiteactive
5Salesonsiteexited
6Legalremoteexited
7Supportonsiteactive
8Supportremotenotice

Output

departmentonsitehybridremotetotal
Engineering0213
Sales1001
Support1012

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 department

Another 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 department

Another 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 →