Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help Simplifying SQL query
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