Query Problem

[subodh_04]

The emp table contains these columns:
EmpName Varchar2 (25)
Salary Number7, 2
You need to display the names of employees on more than an average salary of all employees.
Why does this fail to work :

select EmpName,salary from emp having salary > avg(salary) group by empname,salary ;

I get output as : No rows selected

But when i use sub- query the o/p is perfect :

select EmpName,salary from emp where salary > (select avg(salary) from emp) ;

Why is it so? Please help me.

[pando]

group is done last, you better read some SQL manuals

having acts on the result set, i.e on salary, from where can he get the avg(salary)?

[ales]

What Oracle doc says:
You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.
That's all.

The "not-working" query counts average salary for each employee, which is the employee's salary itself. Then, this "average" salary is compared with salary and only those rows are returned, where salary > salary. None, of course.

Tryselect EmpName,salary from emp having salary = avg(salary) group by empname,salary;and the query should return all rows, because each employee has salary equal to his/her salary.

The second, "working" query is perfect, use it.

Hope that helps.

[pando]

hmm ales what´s the point if the query returns wrong results

[subodh_04]

Thancs for ur instant solutions.

[ales]

Originally posted by pando
hmm ales what´s the point if the query returns wrong results Hi pando, I'm not sure what you want to say here.
The both queries return good results, both do the thing the're asked for.
The first query does not make any sense, however.

[pando]

select EmpName,salary from emp having salary = avg(salary) group by empname,salary;

that returns all rows as you said but he wants the names of employees on more than an average salary of all employees which is only a few!

[ales]

I see now.

My intention was to show that the syntax used in original poster's first query doesn't make sense. Using ">" returns no rows, using "=" returns all rows. Then I suggested to use his/her second query as it works according to requirement.

Sorry for confusing. It wasn't very clear, I admit. :-/

[subodh_04]

Sorry the confusion was started by me.

Ales pls do check ur personal inbox

[slimdave]

Analytical Function Method:
select
EmpName,
salary
from
(
select
empname,
salary,
avg(salary) over () avg_salary
from
emp
)
where
salary > avg_salary;

[subodh_04]

Please explain me what is analytical function?
I couldnt get ur code.

[slimdave]

Originally posted by subodh_04
Please explain me what is analytical function?

They're in the SQL Reference guide. They are basically windowing functions that operate on a result set to perform an aggregate-type function within the set.

for example ...

select
emp,
dept,
salary,
job,
avg(salary) over (partition by dept)
avg_dept_salary,
avg(salary) over (partition by job)
avg_job_salary,
avg(salary) over ()
avg_company_salary
from
emp

... would allow a comparison of every salary with the average employee salary for that department, the average for the company, and the average for the job.

Very powerful stuff.

[bmullin]

WOW
Guess I should read the damn documation.

[pando]

slimdave

do you know how to ensure the inline views is always resolved first :-?

[slimdave]

Originally posted by pando
slimdave

do you know how to ensure the inline views is always resolved first :-?

If the inline view contains an analytical function then i believe it would always be resolved first. I don't think that predicates would get pushed down into the view because that could fundamentally change the result.