CODEWITHSUNDEEP
sqlfunctionjoin
Novice
Novice

Reputation: 145

SQL group functions using joins

Problem: Create a list of department names, the manager id, manager name (employee last name) of that department, and the average salary in each department.

SELECT d.department_name, d.manager_id, AVG(e.salary)
FROM employees e
INNER JOIN departments d ON (e.department_id = d.department_id)
GROUP BY d.department_name, d.manager_id;

And it works nice, but when I add the e.last_name, I get all the last names from employees table.

I do believe the answer to be out here and not quite far, although out of my reach at this point.

Upvotes: 1

Views: 100

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726499

In order to pull the name of the manager, you need to join employees again, this time on d.manager_id:

SELECT d.department_name, d.manager_id, m.name, AVG(e.salary)
FROM employees e
INNER JOIN departments d ON (e.department_id = d.department_id)
LEFT OUTER JOIN employees m ON (m.employee_id = d.manager_id)
GROUP BY d.department_name, d.manager_id, m.name;

The kind of join (inner or outer) is not essential here, because you group by d.manager_id.

Upvotes: 2

Linger
Linger

Reputation: 15048

It looks like you need to join d.manager_id to employees again to get the managers last_name:

SELECT d.department_name, d.manager_id, e2.last_name, AVG(e.salary)
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN employees e2 ON d.manager_id = e2.employee_id
GROUP BY d.department_name, d.manager_id, e2.last_name

Upvotes: 2

Related Questions