SQL to Select the salary which are above the average salary

AnandSudhan · New User Joined: 21 Jun 2006 Posts: 7

Hi ,

I was asked to write a query in an interview in which we have to select the salary which are above the average salary department wise . I had actually gave a subquery which I not sure whether its a right . Assume the table name is emp .
Expecting a convincing answer from the DB2 Experts

DavidatK · Posted: Thu Jun 22, 2006 8:06 pm

This is what I would try. (untested)

MCEVOY · New User Joined: 21 Nov 2005 Posts: 18

DavidatK's suggestion is quite close to the mark, but would use the average salary for all employees. I assume that "department wise" means the employee's own department. So I would extend the suggestion to the following:

SELECT *
FROM DB2.EMP E
WHERE E.SALARY >
(SELECT AVG(E2.SALARY)
FROM DB2.EMP E2
WHERE E2.DEPT = E.DEPT
)

DavidatK · Posted: Thu Jun 22, 2006 8:37 pm

Quite right

Dave

AnandSudhan · New User Joined: 21 Jun 2006 Posts: 7

Hi Dave,

Thanks for the reply . Even I had given like this query I guess the inner query needs to have Group By Clause for selecting the average salary for an particular Department . Since we need multiple results (ie for each department ) .

Correct me if I am wrong .

Hanfur · Active User Joined: 21 Jun 2006 Posts: 104

Does this work?

SELECT DEPT,SALARY FROM EMPLOYEE
WHERE SALARY > AVG(SALARY)
GROUP BY DEPT;

SALARY DEPT
------ ----
100 A1
150 A2
200 A3
100 A1
200 A2
500 A3

AFTER GROUP BY

SALARY DEPT
------ ----
100 A1
100 A1
150 A2
200 A2
200 A3
500 A3

A1 -100,100 AVERAGE = 100
A2 -150,200 AVERAGE = 175
A3 -200,500 AVERAGE = 350

SO SHOULD DISPLAY RESULTS AS

DEPT SALARY
---- -----
A2 200
A3 500

I have not tried this..

-Han

Brian's_song · New User Joined: 27 Dec 2005 Posts: 21

SELECT *
FROM (SELECT DISTINCT(DEPT), AVG(SALARY) AVGSALY
FROM DB2.EMP
GROUP BY DEPT
) A,
DB2.EMP B
WHERE A.DEPT = B.DEPT AND A.SALARY > B.AVGSAL

How do you think this one ?

I have not try it.

Hanfur · Active User Joined: 21 Jun 2006 Posts: 104

Mistake from my side as my query wont work in this scenario as Iam using AVG aggreagte function in the WHERE clause.

MCEVOY solutions works in this scenario as

it gets manages the grouping in the innner query DEPT-wise to get the average salary from each DEPT and outer query only gets the Salary of those in a paricualr DEPT whose salaries exceeds the avg salary of the employees in that DEPT.

Thanks

-Han.