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.

Try

select 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;