Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help Simplifying SQL query

Re: Help Simplifying SQL query

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 19 Mar 2003 15:16:10 -0800
Message-ID: <130ba93a.0303191516.3cfe2a53@posting.google.com>


If I understand what you are looking for, you want all employees that are making above average salary in their respective department? If yes, analytic function should take care of it nicely:

(untested)
select * from (

   select emp_lname, emp_fname, dept_name, salary,

 	salary - avg(salary) over (partition by e.dept_id) sal_dif
        from sales2.employee e, sales2.department d
        where e.dept_id=d.dept_id
              )

wehre sal_dif > 0;

nospam_at_mysticdragon.com (Montse) wrote in message news:<a4beba43.0303190923.61192936_at_posting.google.com>...
> Hi there,
>
> I'm new to SQL, only been working with a few months. I've created
> this query that does exactly what I need. However, it is very
> apparent that I can simplify it as I am reusing the same select
> statement code 3 times.
>
> It seems I should be able place this select statement in the from
> statement once and then use an alias or something to access it earlier
> in the statement.
>
> Any ideas on how to do this without using views?
>
> Thanks in advance!
>
> select emp_lname, emp_fname, dept_name, salary,
> salary -
> (select avg(salary) from sales2.employee e2
> where dept_id=e.dept_id
> ) as Difference,
> (select avg(salary) from sales2.employee e2
> where dept_id=e.dept_id
> ) as AVERAGE
> from sales2.employee e, sales2.department d
> where d.dept_id = e.dept_id
> and
> salary >
> (select avg(salary) from sales2.employee e2
> where dept_id=e.dept_id
> group by dept_id)
> order by dept_name
> /
Received on Wed Mar 19 2003 - 17:16:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US