CODEWITHSUNDEEP
sql
4vol
4vol

Reputation: 15

Highest paid employee + average salary of the departments

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?

Upvotes: 0

Views: 159

Answers (2)

user330315
user330315

Reputation:

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.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108400

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

Upvotes: 0

Related Questions