CODEWITHSUNDEEP
sqlsql-server-2008
grmckb
grmckb

Reputation: 5

How do I find departments with higher average salary than the average salary of the company?

I am trying to output only departments with a higher average salary than the average salary of the company.

SELECT Departments.DEPARTMENT_NAME
FROM Employees inner join departments
ON Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID
WHERE (SELECT AVG(CAST(Employees.salary AS decimal)) 
      GROUP BY Employees.DEPARTMENT_ID) > (SELECT AVG(CAST(Employees.salary AS decimal)))
GROUP BY Departments.DEPARTMENT_NAME;

Upvotes: 0

Views: 153

Answers (1)

jpw
jpw

Reputation: 44881

I think this might be what you want:

SELECT D.DEPARTMENT_NAME
FROM Employees E
INNER JOIN Departments D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
HAVING AVG(CAST(E.Salary AS decimal)) > 
  (SELECT AVG(CAST(Salary AS decimal)) FROM Employees)

Sample SQL Fiddle

Upvotes: 1

Related Questions