Count Employees per Department
Kestrel Labs is buying laptops in bulk and the supplier quotes per team size, so Ops needs a headcount for every team that currently has somebody on it. Teams with nobody on the books need no laptops and should not show up on the order at all.
Ops is happy with the team numbers rather than the team names — the finance system they are pasting this into keys on ids. What they cannot do is read it off the table: there are ten employee rows and fewer teams than that, so the row count answers a question nobody asked.
employees — one row per employee. department_id says which team they belong to. manager_id points at another employee, or is NULL.
| id | name | department_id | salary | hire_date | manager_id |
|---|---|---|---|---|---|
| 1 | Ava Chen | 1 | 145000 | 2021-03-14 | NULL |
| 2 | Ben Ortiz | 1 | 118000 | 2022-06-01 | 1 |
| 3 | Cara Novak | 1 | 121000 | 2023-01-10 | 1 |
| 4 | Deshawn Lee | 2 | 95000 | 2020-09-23 | NULL |
| 5 | Elin Kask | 2 | 88000 | 2022-11-05 | 4 |
| 6 | Farid Amiri | 3 | 76000 | 2023-04-18 | NULL |
| 7 | Grace Kim | 4 | 132000 | 2021-07-30 | NULL |
| 8 | Hugo Silva | 4 | 110000 | 2023-02-14 | 7 |
| 9 | Ines Duarte | 4 | 104000 | 2023-08-01 | 7 |
| 10 | Jonas Weber | 2 | 91000 | 2021-12-19 | 4 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning two columns, department_id and num_employees, with one row per team that has at least one employee on it. Rows come back sorted by department_id, smallest first.
Example output — shape only, on an invented company; both the ids and the counts are made up.
| department_id | num_employees |
|---|---|
| 6 | 12 |
| 7 | 5 |
| 9 | 8 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in