Покажите название компании, название отдела и количество сотрудников в отделе

12. Показать компании, отделы и количество сотрудников в каждом отделе

Условие задачи:
Даны три таблицы:

company — компании;
department — отделы компаний;
employee — сотрудники отделов.

Необходимо вывести:

название компании;
название отдела;
количество сотрудников в этом отделе.

Отделы без сотрудников также должны присутствовать в результате с количеством 0.

Код:

CREATE TABLE company (
    id BIGINT PRIMARY KEY,
    name_ VARCHAR NOT NULL
);

CREATE TABLE department (
    id BIGINT PRIMARY KEY,
    name_ VARCHAR NOT NULL,
    company_id BIGINT NOT NULL,
    FOREIGN KEY (company_id) REFERENCES company(id)
);

CREATE TABLE employee (
    id BIGINT PRIMARY KEY,
    name_ VARCHAR NOT NULL,
    department_id BIGINT NOT NULL,
    FOREIGN KEY (department_id) REFERENCES department(id)
);

INSERT INTO company (id, name_) VALUES
(1, 'Company 1'),
(2, 'Company 2'),
(3, 'Company 3');

INSERT INTO department (id, name_, company_id) VALUES
(1, 'Department 1', 1),
(2, 'Department 2', 1),
(3, 'Department 3', 2);

INSERT INTO employee (id, name_, department_id) VALUES
(1, 'Employee 1', 1),
(2, 'Employee 2', 2),
(3, 'Employee 3', 3);

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

Подсказки

💡 Соедини company и department по company.id = department.company_id.
💡 Для сотрудников используй LEFT JOIN, чтобы не потерять отделы без сотрудников.
💡 Количество сотрудников можно посчитать через COUNT(employee.id).
💡 Сгруппируй результат по компании и отделу.

Решение

SELECT
    c.name_ AS company_name,
    d.name_ AS department_name,
    COUNT(e.id) AS employee_count
FROM company c
JOIN department d
    ON d.company_id = c.id
LEFT JOIN employee e
    ON e.department_id = d.id
GROUP BY
    c.id,
    c.name_,
    d.id,
    d.name_
ORDER BY
    c.name_,
    d.name_;

JOIN связывает компании с их отделами.

Для сотрудников используется:

LEFT JOIN employee e
    ON e.department_id = d.id

поэтому отдел без сотрудников не исчезнет из результата.

Важно считать именно:

COUNT(e.id)

а не COUNT(*): при отсутствии сотрудников COUNT(e.id) вернёт 0.

Для приведённых данных результат будет:

company_name | department_name | employee_count
-------------|-----------------|---------------
Company 1    | Department 1    | 1
Company 1    | Department 2    | 1
Company 2    | Department 3    | 1

Компания без отделов в такой результат не попадёт, поскольку вывод строится по отделам.