Help Correct a query 1

[tekpr00]

Can anyone help correct this query.

The question is Write a query that returns all departments with average salary higher than 5000

so I attempted to find the average as follows:
SELECT Avg(salary)
FROM employees
WHERE salary > 5000

this returns
9298,27586206896551724137931034482758621

I tried to write a nested query on the same table as gollows

select department
from employees
where salary in (SELECT Avg(DISTINCT salary)
FROM employees
WHERE salary > 5000);

However this is returning "No data found"

Please help

 

[tekpr00]

I believe I solved this issue by correcting it as follows
select department
from employees
where salary > (SELECT Avg(DISTINCT salary)
FROM employees
WHERE salary > 5000);

 

[carp]

Try

SELECT department
  FROM employees
 GROUP BY department
HAVING avg(salary) > 5000;

It only requires one visit to the table and will give you a more compact result set.

You don't want to do an avg(DISTINCT salary), since that may skew your results. Consider the case where the salaries in the department are for three people, and their salaries are 6000, 6000, and 3000. The true average is 5000, but if you take the average of distinct salaries, the result would be (3000+6000)/2 = 4500. Thus, while this department should be included in the list, it will not be and your result set will be wrong.

Another problem with your approach is that by specifying "WHERE salary > 5000", you are filtering out any employees whose salary is <= 5000. As a result, all departments that have at least one employee with a salary > 5000 will be shown, regardless of what their true average salary is.

Remember - HAVING acts as a filter on groups (which is what you want in this case) while WHERE is a filter on individual records (which you do NOT want here).

 

[tekpr00]

thanks Carp

 

[BJCooperIT]

Great explanation, Carp. Have a star on me.

[sup]Beware of false knowledge; it is more dangerous than ignorance. ~ George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)

 

[carp]

Thanks, BJ - I appreciate it!