CODEWITHSUNDEEP
sql
user1915345
user1915345

Reputation: 49

Retrieve info from table

I need to retrieve the info of the employees that earn more money than the average wage of their department... we have departments named 10, 20, 30, 40, 50, ... and so goes on. Here I have managed to retrieve what I need for only one department. (40) How can I do it for as many departments as there may be?

This is my Query:

SELECT  * FROM    EMPLOYEE where (Department_ID='40')and 
 (
 employee_salary > 
  (select avg(employee_salary) from EMPLOYEE  where  Department_ID='40')   
 )

Datatable: data table

Upvotes: 0

Views: 93

Answers (4)

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

Hope this will do,

    SELECT  emp.* FROM    EMPLOYEE emp where emp.employee_salary >
      (  select avg(employee_salary) from EMPLOYEE new1 
         where emp.Department_ID=new1.Department_ID 
         group by  Department_ID  
      )

Upvotes: 4

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Try this Query

select * from EMPLOYEE as e1
where e1.employee_salary > (select avg(employee_salary) 
                         from EMPLOYEE as e2 
                         where e1.department_id=e2.department_id
                         group by Department_id)

Upvotes: 0

tom
tom

Reputation: 19153

select e.* from employee as e inner join
    (select department_id, avg(employee_salary) as avg_salary
        from employee group by department_id) as f
on e.department_id = f.department_id
where e.employee_salary > f.avg_salary;

Upvotes: 0

Artem Olanovsky
Artem Olanovsky

Reputation: 1

you can try this :

    SELECT  *, avg(employee_salary) as average_salary 
FROM    EMPLOYEE 
where Department_ID='40' 
having average_salary<employee_salary

Upvotes: 0

Related Questions