CODEWITHSUNDEEP
mysqlaverage
Andrew P
Andrew P

Reputation: 1

MySQL newbie: Query to find the name of each employee whose salary exceeds the average salary of all employees in his or her department

Print the name of each employee whose salary exceeds the average salary of all employees in his or her department.

This is what I have:

SELECT ename FROM emp
WHERE salary > all (
  SELECT AVG(salary) FROM dept, works
  WHERE emp.eid = works.eid AND works.did = dept.did)

The problem is that I seem to be getting the names of people who have a salary greater than than the average for EVERY worker. I'm thinking I don't need a link to the department table, but when I tried editing the string above, I still get the same result.

Upvotes: 0

Views: 3792

Answers (2)

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

SELECT emp.ename,dept.dname,emp.salary,avg(emp.salary) as DeptAvgSalary
FROM emp 
INNER JOIN works ON emp.eid = works.eid
INNER JOIN dept ON works.did = dept.did
group by dept.did 
having emp.salary>DeptAvgSalary

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26343

Your approach using a subquery for the average is sound, but you need to group the subquery by department. Then you can join to the subquery by:

  • Department ID's are equal (equijoin), and
  • Employee salary is greater than than the average departmental salary (non-equijoin)

Here's the query...

SELECT emp.ename, dept.dname, emp.salary, DeptAvg.AvgSal
FROM emp
INNER JOIN works ON emp.eid = works.eid
INNER JOIN dept ON works.did = dept.did
INNER JOIN (
    SELECT works.did, AVG(emp.salary) AS AvgSal
    FROM emp
    INNER JOIN works ON emp.eid = works.eid
    GROUP BY works.did) DeptAvg
  ON DeptAvg.did = works.did AND emp.salary > DeptAvg.AvgSal

This query shows employee name, department name, employee salary and average departmental salary. I did that so you can see the numbers and test it. You can remove any of the columns and the query should still work.

Upvotes: 1

Related Questions