Количество сотрудников и количество заказчиков для каждого офиса онлайн урок.


Разобьем это задание на составляющие.

Рассмотрим запрос, при котором необходимо получить количество сотрудников на каждый офис. Для этого необходимо применить группировку по идентификатору офиса и для каждой группы подсчитать количество сотрудников:

SELECT o.id as office_id, COUNT(w.id) as worker_count FROM offices o JOIN workers w ON o.id = w.office_id GROUP BY o.id


+-----------+--------------+
| office_id | worker_count |
+-----------+--------------+
| 1 | 6 |
| 2 | 2 |
| 3 | 2 |
| 4 | 5 |
| 5 | 2 |
| 6 | 4 |
| 7 | 2 |
+-----------+--------------+


Тут мы соединили столбцы из таблицы с офисами и столбцы из таблицы сотрудников. Этого можно было не делать, т.к. поле с идентификатором офиса есть в таблице сотрудников. Написанный запрос дает возможность дополнительно получить информацию о каждом офисе.

Так мы подсчитали количество сотрудников на офис. Теперь нам надо дополнить этот результат количеством заказчиков на каждый офис. Стоит отметить, что необходимо будет использовать LEFT JOIN т.к. могут быть сотрудники, которые не прикреплены к заказчикам, но они должны быть включены в количество сотрудников по офису.

Попробуем написать этот запрос:

SELECT o.id as office_id, COUNT(w.id) as worker_count, COUNT(c.id) as customer_count 
FROM offices o
JOIN workers w ON o.id = w.office_id
LEFT JOIN customers c ON w.id = c.worker_id
GROUP BY o.id


+-----------+--------------+----------------+
| office_id | worker_count | customer_count |
+-----------+--------------+----------------+
| 1 | 38 | 34 |
| 2 | 12 | 12 |
| 3 | 15 | 15 |
| 4 | 30 | 29 |
| 5 | 6 | 5 |
| 6 | 12 | 10 |
| 7 | 17 | 17 |
+-----------+--------------+----------------+


Этот запрос еще не до конца правильный. Предположим у нас есть два клиента, которые привязаны к одному и тому же сотруднику. Тогда этот сотрудник будет дважды прибавлен к общему количеству сотрудников. Для того, чтобы убедиться в этом, рассмотрим запрос:

SELECT o.id as office_id, w.id as worker_id, c.id as customer_id FROM offices o 
JOIN workers w ON o.id = w.office_id
LEFT JOIN customers c ON w.id = c.worker_id

В результирующей таблице мы видим повторения значений в поле идентификатора сотрудника.

Выход из этой ситуации простой: необходимо дописать оператор DISTINCT в функции COUNT для подсчета сотрудников. Тогда дубли не будут учитываться при суммировании.

SELECT o.id as office_id, COUNT(DISTINCT w.id) as worker_count, COUNT(c.id) as customer_count FROM offices o 
JOIN workers w ON o.id = w.office_id
LEFT JOIN customers c ON w.id = c.worker_id
GROUP BY o.id


+-----------+--------------+----------------+
| office_id | worker_count | customer_count |
+-----------+--------------+----------------+
| 1 | 6 | 34 |
| 2 | 2 | 12 |
| 3 | 2 | 15 |
| 4 | 5 | 29 |
| 5 | 2 | 5 |
| 6 | 4 | 10 |
| 7 | 2 | 17 |
+-----------+--------------+----------------+