Highest paid employee + average salary of the departments

0
votes

An employee belongs to a department (foreign key = D_ID). An employee has a SSN (primary key), name, salary and D_ID.

A department has multiple employees (primary key = ID)

I want a query that returns Department name| Name of Highest Paid Employee of that department | His Salary | Average salary of employees working in the same department.

I know how to select the first part: 

SELECT 
    D.name, E.name, E.salary 
FROM 
    Employee E, Department D 
WHERE
    salary IN (SELECT MAX(E.salary) 
               FROM Employee E 
               GROUP BY E.D_ID)  
    AND E.D_ID = D.ID

I know also how to select the last part:

SELECT AVG(E.salary) 
FROM Employee E 
GROUP BY E.D_ID

How do I put these together in a single query?

2
sql
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged - marc_s
The "salary IN" condition could cause rows to be returned for Employees that do not have the maximum salary in their department, but do have a salary that happens to match the maximum salary of some other department. (And its time to ditch the old school comma syntax for the join operation.) - spencer7593
@spencer7593, very good observation, thanks! - 4vol

2 Answers

1
votes

You can use window functions for that: 

select department_name, employee_name, salary, avg_dept_salary 
from (
  select e.name as employee_name, 
         d.name as department_name, 
         e.salary,
         max(e.salary) over (partition by d.id) as max_dept_salary, 
         avg(e.salary) over (partition by d.id) as avg_dept_salary
  from Employee E
    join Department D on e.d_id = d.id
) t 
where salary = max_dept_salary
order by department_name;

The above is standard ANSI SQL and runs on all modern DBMS.

0
votes

I would do something like this:

SELECT d.name
     , e.name
     , e.salary
     , n.avg_salary
  FROM Department d
  JOIN ( SELECT m.d_id
              , MAX(m.salary) AS max_salary
              , AVG(m.salary) AS avg_salary 
           FROM Employee m
          GROUP BY m.d_id
       ) n
    ON n.d_id = d.id

  JOIN Employee E
    ON e.d_id   = d.id
   AND e.salary = n.max_salary