A little trick on average

kumarjit · Post by **kumarjit** » Mon Mar 02, 2015 10:39 am

Hello all.

Pleasure writing back in this forum after a pretty long time.
At the outset, I'd like to surrender that I tried to search through all the post, but somehow did not get any relevant posts,
but if there are any, please let me know of it.

I'm trying to retrieve records from a table called EMPLOYEE where employee salary is greater than or equal to the
average sal of the concerned department

the table schema is as follows:
EMPID(integer)
EMPNAME(string)
SAL(integer)
DEPTCD(integer)

The way I did it is:

Code: Select all

                    Oracle Connector(to fetch DEPTCD, AVG(SAL) as AVG_SAL GROUP BY DEPTCD)                       
                            |
                            |
                            |
                            |
Oracle Connector----------Join-----------Filter-----------------Output Data Set
(to read from          (on DEPTCD)     (where SAL>=AVG_SAL)
EMPLOYEE table)

The problem I see here is using two Oracle Connectors to read the same table twice.
Is there a better way of doing this?

Thanks & Regards,
Kumarjit.

PaulVL · Post by **PaulVL** » Mon Mar 02, 2015 11:30 am

If it's the same oracle database, just do it all in sql.

chulett · Post by **chulett** » Mon Mar 02, 2015 11:51 am

However, in my opionion there's nothing wrong with how you've done it... I assume it is working for you, yes?

stuartjvnorton · Post by **stuartjvnorton** » Mon Mar 02, 2015 6:38 pm

Read -> Agg (mean value) -> Filter -> Done ?

kumarjit · Post by **kumarjit** » Mon Mar 02, 2015 10:14 pm

chulett wrote:However, in my opionion there's nothing wrong with how you've done it... I assume it is working for you, yes?

Yes it does Craig.... :D
What I'm seeking for is an alternative approach, where I'll be using a single Oracle Connector to fetch data from the table once and for all, and then use them to find the AVERAGE SAL/ DEPTCD , and then filter accordingly....

Any help????

Btw, thanks for such a quick turnaround....... Pleasure seeing you write back after a long while.

Regards,
Kumarjit.

kumarjit · Post by **kumarjit** » Mon Mar 02, 2015 10:33 pm

stuartjvnorton wrote:Read -> Agg (mean value) -> Filter -> Done ?

Didn't understand how could you achieve this using a single read?
Please explain..

Regards,
Kumarjit.

chulett · Post by **chulett** » Mon Mar 02, 2015 10:55 pm

As Paul noted, you can do it all in one SQL although it will basically emulate exactly what you are doing in the job. Rather quickly and off the top of my head, something like:

Code: Select all

select e.EMPID, e.EMPNAME, e.DEPTCD, e.SAL, da.AVG_SAL
from EMPLOYEE e,
     (select DEPTCD, AVG(SAL) as AVG_SAL
       from EMPLOYEE
      group by DEPTCD) da
  where e.DEPTCD = da.DEPTCD
    and e.SAL > da.AVG_SAL

Should be pretty close. I hope.

kumarjit · Post by **kumarjit** » Tue Mar 03, 2015 2:22 am

Close????? Its more than close eh!!....

Thanks to all of you for your support.

Regards.