Просьба помочь исправить SQL запрос
Задача:
Выгрузи таблицу из двух полей: первое поле с фамилией сотрудника, второе — с количеством пользователей, чьи запросы этот сотрудник обработал. Назови поля employee_last_name и all_customers соответственно. Сгруппируй записи по идентификатору сотрудника. Отсортируй количество пользователей по убыванию.
*К таблице staff присоедини таблицу client с помощью оператора LEFT OUTER JOIN*
Запрос:
SELECT l.last_name AS employee_last_name,
i.customer_id AS all_customers
FROM client as i
LEFT OUTER JOIN client AS l ON i.customer_id=l.customer_id
GROUP BY i.customer_id
ORDER BY i.customer_id DESC;
В ответе:
column "l.last_name" must appear in the GROUP BY clause or be used in an aggregate function
SELECT l.last_name AS employee_last_name,
^^^
i.customer_id AS all_customers
FROM client as i
LEFT OUTER JOIN client AS l ON i.customer_id=l.customer_id
GROUP BY i.customer_id
ORDER BY i.customer_id DESC;
SELECT s.last_name AS employee_last_name,
COUNT(c.customer_id) AS all_customers
FROM staff as s
LEFT OUTER JOIN client AS c ON s.employee_id= c.support _rep_id
GROUP BY s.employee_id
ORDER BY all_customers DESC,
s.last_name;
SELECT s.last_name AS employee_last_name,
COUNT(DISTINCT c.customer_id) AS all_customers
FROM staff as s
LEFT OUTER JOIN client AS c ON s.staff_id=c.staff_id
GROUP BY s.last_name
ORDER BY COUNT(DISTINCT c.customer_id) DESC;
Ну добавьте агрегатную функцию (кстати в группировке полей маловато)
SELECT s.last_name AS employee_last_name,
COUNT(c.customer_id) AS all_customers
FROM staff AS s
LEFT OUTER JOIN client AS c ON s.employee_id = c.support _rep_id
GROUP BY s.employee_id
ORDER BY all_customers DESC