find average salary

[harikumar]

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

[stuartjvnorton]

Pick a data connector.
Write the custom SQL query to retrieve the relevant records.
Do something with those records.
Go home early: you've earned it.

[ray.wurlod]

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.

[Sainath.Srinivasan]

You requirement falls into reporting category than ETL.

Especially if you append the data the averages will change.

Use a reporting tool to achieve your goal.

[grimm336]

Use the sql in your database stage....
select sal from employee where sal>avg(sal)

And link the out put to a flat file which is having same meta data as the table...

cheers

[ray.wurlod]

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)

[chulett]

In other words, two passes through the data.

[laknar]

Below is the Query you are looking for.

Code: Select all

SELECT EMPNO,SALARY FROM TABLENAME WHERE SALARY>(SELECT AVG(SALARY) FROM TABLENAME) 

[ray.wurlod]

That looks vaguely familiar yet, at the same time, somehow wrong.