CODEWITHSUNDEEP
mysqlsql
Τάκης Μπαλσαμίδης
Τάκης Μπαλσαμίδης

Reputation: 41

SELECT every employee that has a higher salary than the AVERAGE of his department

I have only 1 table named EMPLOYEE on my database with the 3 following collumns:

Employee_Name, Employee_Salary, Department_ID

Now I have to SELECT every employee that has a higher salary than the AVERAGE of his department. How do I do that?

The main problem that I have is that when comparing each Employee_Salary with 

SELECT  AVG(department_ID) FROM employee GROUP BY Department_ID

the return set of the inner queue returns multiple rows.

I think I need to perform a join operation but I do not know how.

Upvotes: 4

Views: 10924

Answers (8)

Rdzen Tech
Rdzen Tech

Reputation: 1

with EmployeesAvg as ( select AVG(Salary) as average, Department from Employees group by department ) select * from EmployeesAvg empEVG inner join Employees emp on emp.Salary>empEVG.average and emp.department = empEVG.department

Upvotes: -1

Ray Bryant
Ray Bryant

Reputation: 1

Try this also:

Select e.ename, e.sal, e.deptno
from (select e.*, avg(sal) over (partition by deptno) as avgsalary
      from EMP_TABLE e
     ) e
where e.sal > e.avgsalary;

Upvotes: -1

Amol Baheti
Amol Baheti

Reputation: 11

Assuming Postgres,

Try This

select e1.* from emp e1  inner join (select avg(sal) avg_sal,dept_id from emp group by dept_id) as e2 on e1.dept_id=e2.dept_id and e1.sal>e2.avg_sal

Upvotes: 1

Sam
Sam

Reputation: 410

you can try this way as well !

  select FirstName,E.DepartmentName,BaseRate,EB.avgSAL
  From DimEmployee E
  inner join
  (select avg(BaseRate) As avgSAL,DepartmentName
  from DimEmployee
  group by DepartmentName ) EB
  ON E.DepartmentName = Eb.DepartmentName
  where E.BaseRate > Eb.avgSAL

Upvotes: 0

Edper
Edper

Reputation: 9322

Try using EXISTS() like:

SELECT t1.Employee_Name, t1.Employee_Salary, t1.Department_ID
FROM Employee t1
WHERE EXISTS
(
 SELECT t2.Department_ID, AVG(t2.Employee_Salary) as AvgSalary
 FROM Employee t2
 WHERE   t1.Department_ID = t2.Department_ID
 GROUP BY t2.Department_ID
 HAVING t1.Employee_Salary>AVG(t2.Employee_Salary)

);

See Fiddle Demo

Upvotes: 0

bvr
bvr

Reputation: 4826

Try this without GROUP BY

SELECT * FROM employee  E1 
WHERE [Employee_Salary] > (
                            SELECT AVG([Employee_Salary]) FROM employee  E2 
                            WHERE E2.[Department_ID] = E1.[Department_ID]
                           )

SQL FIDDLE DEMO

Upvotes: 0

naveen goyal
naveen goyal

Reputation: 4629

Try This... This not tested. 

SELECT * from employee join (SELECT AVG(employee_salary) as sal, department_ID 
FROM employee GROUP BY Department_ID) as t1 
ON employee.department_ID = t1.department_ID 
where employee.employee_salary > t1.sal

Upvotes: 0

Roshni Shah
Roshni Shah

Reputation: 309

please try with below query

Select * from employee a where Employee_Salary > (select avg(Employee_Salary) from 
employee b group by Department_ID having b.Department_ID = a.Department_ID)

or

Select * from employee a where Employee_Salary> (select avg(Employee_Salary) from 
employee b where b.Department_ID = a.Department_ID group by Department_ID)

Upvotes: 4

Related Questions