find average salary
Moderators: chulett, rschirm, roy
find average salary
i have source table like
empno,salary
10,1000
20,4000
30,5000
10,3000
30,5000
40,6000
i want to do find who are the employees having salary more than average salary
cal anybody tell how can we solve this using stages
empno,salary
10,1000
20,4000
30,5000
10,3000
30,5000
40,6000
i want to do find who are the employees having salary more than average salary
cal anybody tell how can we solve this using stages
-
- Participant
- Posts: 527
- Joined: Thu Apr 19, 2007 1:25 am
- Location: Melbourne
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You need two passes through the data - just like you would in SQL (an outer query and an inner query). In the parallel world that will involve two jobs, since blocking operations are not permitted. The first job gets the average salary - probably using an Aggregator stage - and stores that somewhere that the controlling sequence can pick up and supply to the second job as a parameter value. The second job filters your data records according to requirement, using the parameter value as the baseline average salary.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
My guess is that that SQL will throw a syntax error of the form "avg function used with no grouping column". This has to be a nested query.
Code: Select all
select sal from employee where sal > (select avg(sal) from employee)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Below is the Query you are looking for.
Code: Select all
SELECT EMPNO,SALARY FROM TABLENAME WHERE SALARY>(SELECT AVG(SALARY) FROM TABLENAME)
Regards
LakNar
LakNar
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: