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: Isaac Blank <izblank_at_yahoo.com>
Date: Wed, 19 Mar 2003 20:25:57 GMT
Message-ID: <pn4ea.3894$LZ2.215753924@newssvr21.news.prodigy.com>


select emp_lname, emp_fname, dept_name, salary, salary - average as Difference,
AVERAGE
from sales2.employee e, sales2.department d, (select dept_id, avg(salary as average) from sales2.employee group by dept_id) e2
where d.dept_id = e.dept_id
and d.dept_id=e2.dept_id
and salary > average
order by dept_name

"Montse" <nospam_at_mysticdragon.com> 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 - 14:25:57 CST

Original text of this message

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