Количество сотрудников по отделам и отделы с >3 сотрудниками с ЗП > 100к | IT Ментор

58. Посчитать сотрудников по отделам и найти отделы с четырьмя и более высокооплачиваемыми сотрудниками

Даны таблицы departments и employees.

Необходимо написать два SQL-запроса:

Посчитать количество сотрудников в каждом отделе.
Найти отделы, в которых работает больше трёх сотрудников с зарплатой выше 100000.

Код:

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT REFERENCES departments(id),
    salary INT NOT NULL
);

INSERT INTO departments (id, name) VALUES
    (1, 'Отдел ИТ'),
    (2, 'Отдел ИБ'),
    (3, 'HR');

INSERT INTO employees (id, name, department_id, salary) VALUES
    (1, 'Иван Петров', 1, 120000),
    (2, 'Елена Сидорова', 1, 110000),
    (3, 'Алексей Иванов', 1, 105000),
    (4, 'Мария Кузнецова', 1, 95000),
    (5, 'Дмитрий Смирнов', 2, 150000),
    (6, 'Ольга Васильева', 2, 130000),
    (7, 'Сергей Миронов', 2, 80000),
    (8, 'Анна Жукова', 3, 115000),
    (9, 'Павел Белов', 3, 125000),
    (10, 'Наталья Воробьева', 3, 140000),
    (11, 'Артем Громов', 3, 98000),
    (12, 'Виктория Орлова', 1, 108000);

Спойлеры к решению

Подсказки

💡 Для подсчёта сотрудников сгруппируй данные по отделам.
💡 Если нужно сохранить отделы без сотрудников, начинай запрос с departments и используй LEFT JOIN.
💡 При LEFT JOIN считай COUNT(e.id), а не COUNT(*).
💡 Во второй задаче сначала отфильтруй сотрудников с salary > 100000, затем сгруппируй их по отделам.
💡 Условие на количество сотрудников после группировки задаётся через HAVING COUNT(*) > 3.

Решение

Количество сотрудников в каждом отделе:

SELECT
    d.id AS department_id,
    d.name AS department_name,
    COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e
    ON e.department_id = d.id
GROUP BY
    d.id,
    d.name
ORDER BY d.id;

LEFT JOIN позволяет вывести даже отделы без сотрудников. Для такого отдела COUNT(e.id) вернёт 0.

Для приведённых данных:

department_id | department_name | employee_count
--------------+-----------------+---------------
1             | Отдел ИТ        | 5
2             | Отдел ИБ        | 3
3             | HR              | 4

Отделы, где больше трёх сотрудников получают зарплату выше 100000:

SELECT
    d.id AS department_id,
    d.name AS department_name,
    COUNT(*) AS high_paid_count
FROM departments d
JOIN employees e
    ON e.department_id = d.id
WHERE e.salary > 100000
GROUP BY
    d.id,
    d.name
HAVING COUNT(*) > 3
ORDER BY d.id;

Сначала:

WHERE e.salary > 100000

оставляет только сотрудников с зарплатой выше 100000.

После группировки условие:

HAVING COUNT(*) > 3

оставляет только отделы, где таких сотрудников больше трёх.

Для приведённых данных условию соответствует только Отдел ИТ: в нём четыре сотрудника получают зарплату выше 100000.