Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Related to Average 1

Status
Not open for further replies.

Bhashi

Programmer
Sep 13, 2022
15
LK
HI All,

I Have a table like below,
Code:
[b]EmpID  Name  Salary  Department[/b]

I want to find the employees in each department who earn more than average salary in that department. How to write this query in SQL. Please help. I'm new to SQL Programming.

I tried the below query but it did not give the correct output.[ponder]

Code:
select * from Employee E1 where Salary > (select avg(salary) from Employee E2  where E2.Department = E1.Department )


Thank you.
 
There will be a couple of steps to do. First you need to get the 'average salary in that [every] department.'
[tt][blue]
Select Department, AVG(Salary) As AVG_Salary
From Employee Group By Department[/blue][/tt]

And the second step would be to incorpoprate step one into:

[pre]Select E.EmpID, E.Name, E.Salary, A.AVG_Salary, E.Department
From Employee E,
([blue]Select Department, AVG(Salary) As AVG_Salary
From Employee Group By Department[/blue]) A
Where E.Department = A.Department
And E.Salary > A.AVG_Salary[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top