Noruj.com
easy

Count of Employees per Department

You are a data analyst at BrightForce HR Solutions, a company that manages workforce data for mid-sized enterprises. The HR team has asked you to generate a headcount report showing how many employees currently work in each department. This report will be used during the quarterly planning meeting to identify understaffed or overstaffed teams.

The database contains two tables: departments — which stores department details including department_id and department_name — and employees — which contains employee records including employee_id, first_name, last_name, department_id, and hire_date. Each employee belongs to exactly one department via the department_id foreign key.

Your task is to write a SQL query that returns each department name along with the total number of employees in that department. The results should be ordered by employee count in descending order, so the largest teams appear first. Departments with no employees should still appear in the results with a count of zero.

  • Join the departments table with the employees table using a LEFT JOIN on department_id so departments with zero employees are included.

  • Use COUNT(e.employee_id) to count employees — do not use COUNT(*) as it will incorrectly count rows for empty departments.

  • Group results by department_id and department_name.

  • Return columns: department_name and employee_count.

  • Sort output by employee_count in descending order.

Hint: A common pitfall is using COUNT(*) instead of COUNT(e.employee_id). When using a LEFT JOIN, departments with no employees will still produce one row with NULL values for employee columns — COUNT(*) would count that row as 1, whereas COUNT(e.employee_id) correctly returns 0 because it ignores NULL values.

1
2
3
4
5
Preparing...
Count of Employees per Department

You are a data analyst at BrightForce HR Solutions , a company that manages workforce data for mid-sized enterprises. The HR team has asked you to generate a headcount report showing how many employees currently work in each department. This report will be used during the quarterly planning meeting to identify understaffed or overstaffed teams. The database contains two tables: departments — which stores department details including department_id and department_name — and employees — which contains employee records including employee_id , first_name , last_name , department_id , and hire_date . Each employee belongs to exactly one department via the department_id foreign key. Your task is to write a SQL query that returns each department name along with the total number of employees in that department. The results should be ordered by employee count in descending order , so the largest teams appear first. Departments with no employees should still appear in the results with a count of zero. Join the departments table with the employees table using a LEFT JOIN on department_id so departments with zero employees are included. Use COUNT(e.employee_id) to count employees — do not use COUNT(*) as it will incorrectly count rows for empty departments. Group results by department_id and department_name . Return columns: department_name and employee_count . Sort output by employee_count in descending order. Hint: A common pitfall is using COUNT(*) instead of COUNT(e.employee_id) . When using a LEFT JOIN , departments with no employees will still produce one row with NULL values for employee columns — COUNT(*) would count that row as 1, whereas COUNT(e.employee_id) correctly returns 0 because it ignores NULL values.

Table Setup (SQL Schema)

CREATE TABLE departments (
  department_id SERIAL PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  department_id INT REFERENCES departments(department_id),
  hire_date DATE NOT NULL
);

INSERT INTO departments (department_name) VALUES
  ('Engineering'),
  ('Marketing'),
  ('Sales'),
  ('Human Resources'),
  ('Finance'),
  ('Legal');

INSERT INTO employees (first_name, last_name, department_id, hire_date) VALUES
  ('Alice', 'Nguyen', 1, '2019-03-15'),
  ('Bob', 'Smith', 1, '2020-07-01'),
  ('Carol', 'Patel', 1, '2021-01-20'),
  ('David', 'Kim', 1, '2022-05-10'),
  ('Eva', 'Torres', 2, '2018-11-03'),
  ('Frank', 'Müller', 2, '2021-06-14'),
  ('Grace', 'Chen', 2, '2023-02-28'),
  ('Henry', 'Osei', 3, '2020-09-09'),
  ('Isla', 'Rossi', 3, '2021-12-01'),
  ('James', 'Yamamoto', 4, '2017-04-22'),
  ('Karen', 'Dubois', 4, '2019-08-30'),
  ('Liam', 'Bakker', 5, '2022-03-17'),
  ('Mia', 'Fernandez', 1, '2023-07-05'),
  ('Noah', 'Johansson', 3, '2020-01-13'),
  ('Olivia', 'Martins', 2, '2022-10-22');
Home Videos Quiz Blog