Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQL to Select the salary which are above the average salary

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
AnandSudhan

New User


Joined: 21 Jun 2006
Posts: 7

PostPosted: Thu Jun 22, 2006 5:32 pm    Post subject: SQL to Select the salary which are above the average salary
Reply with quote

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
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Jun 22, 2006 8:06 pm    Post subject: Re: SQL Query
Reply with quote

This is what I would try. (untested)


Code:


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

 


Dave
Back to top
View user's profile Send private message
MCEVOY

New User


Joined: 21 Nov 2005
Posts: 18

PostPosted: Thu Jun 22, 2006 8:32 pm    Post subject:
Reply with quote

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
)
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Jun 22, 2006 8:37 pm    Post subject: Re: SQL Query
Reply with quote

Quite right icon_smile.gif

Dave
Back to top
View user's profile Send private message
AnandSudhan

New User


Joined: 21 Jun 2006
Posts: 7

PostPosted: Fri Jun 23, 2006 10:42 am    Post subject:
Reply with quote

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 .
Back to top
View user's profile Send private message
Hanfur

Active User


Joined: 21 Jun 2006
Posts: 104

PostPosted: Fri Jun 30, 2006 4:08 pm    Post subject: Hi
Reply with quote

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
Back to top
View user's profile Send private message
Brian's_song

New User


Joined: 27 Dec 2005
Posts: 21

PostPosted: Fri Jun 30, 2006 7:51 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Hanfur

Active User


Joined: 21 Jun 2006
Posts: 104

PostPosted: Mon Jul 03, 2006 10:19 am    Post subject: Hi
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm
No new posts Query to select Null row on priority RahulG31 DB2 24 Thu Jan 21, 2016 5:45 pm
No new posts Can I use sort to select sample recor... Steve Ironmonger DFSORT/ICETOOL 3 Tue Nov 10, 2015 10:02 pm
No new posts Performance issue in the DB2 program ... Pradst57 DB2 19 Mon Oct 12, 2015 8:31 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us