Aggregate function and join on 2 conditions

Question

I have this task for Oracle. It requires me to display the departments names(department is a table containing the manager_id for that table, department_id and department_name), the manager_id for that department, the name of the manager(found in employees table) and the average salary for that department(salary of each employee is also found in employees). As soon as I try to retrieve the manager name(I suppose by comparing his id in dept with the one in employees) it messes up my averages. Without it(Like following) it works just fine

SELECT d.department_name, AVG(e.salary) as "Salaries"
FROM   employees e join departments d on e.department_id=d.department_id
WHERE  d.manager_id=e.employee_id
GROUP BY e.department_id, d.department_name,d.manager_id
ORDER BY AVG(e.salary)

Can someone help me solve this and perhaps explain why I mess it up?

Answer 1

WITH factoring clause selects average salary; it is then joined to other tables

SQL> with avgsal as
  2    (select e.department_id, round(avg(e.salary)) avg_sal
  3     from employees e
  4     group by e.department_id
  5    )
  6  select d.department_id,
  7         d.department_name,
  8         m.last_name manager,
  9         a.avg_sal
 10  from departments d
 11  join employees m on m.department_id = d.department_id
 12       and m.employee_id = d.manager_id
 13  join avgsal a on a.department_id = d.department_id
 14  order by a.avg_sal;

DEPARTMENT_ID DEPARTMENT_NAME      MANAGER                      AVG_SAL
------------- -------------------- ------------------------- ----------
           50 Shipping             Fripp                           3476
           30 Purchasing           Raphaely                        4150
           10 Administration       Whalen                          4400
           60 IT                   Hunold                          5760
           40 Human Resources      Mavris                          6500
          100 Finance              Greenberg                       8601
           80 Sales                Russell                         8956
           20 Marketing            Hartstein                       9500
           70 Public Relations     Baer                           10000
          110 Accounting           Higgins                        10154
           90 Executive            King                           19333

11 rows selected.

SQL>

A quick check for 2 departments:

SQL> select department_id, round(avg(salary)) avg_sal, count(*) "# of employees"
  2  from employees
  3  where department_id in (20, 30)
  4  group by department_id
  5  order by 1;

DEPARTMENT_ID    AVG_SAL # of employees
------------- ---------- --------------
           20       9500              2
           30       4150              6

SQL>

Answer 2

Introduce a correlated subquery in select for manager name in your query.

SELECT d.department_name,
       AVG(e.salary) AS "Salaries",
       (SELECT first_name
               ||' '
               ||last_name
        FROM   employees i
        WHERE  i.employee_id = d.manager_id) AS manager_name
FROM   employees e
       join departments d
         ON e.department_id = d.department_id
GROUP  BY e.department_id,
          d.department_name,
          d.manager_id
ORDER  BY Avg(e.salary);