Re: Help Simplifying SQL query

Issac,

Thanks. That's exactly the push I needed. It worked...I needed to changed the parenthesis a bit, to get rid of an error.

Here's what worked though:

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

"Isaac Blank" <izblank_at_yahoo.com> wrote in message news:<pn4ea.3894$LZ2.215753924_at_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 Thu Mar 20 2003 - 09:55:35 CST