Tim Tim - 1 year ago 83
SQL Question

Group By Error in Nested Function

I have a data table Employees. I want to show the employees who earn more than the average salary of all employees in the employees table. The code I have written so far is:

select employee_id, last_name, salary
from employees
group by employee_id
having salary > (select avg(sum(salary))
from employees);

However, when I run this, a "nested function without group by" error occurred. How do I fix this?

Answer Source

GROUP BY unnecessary. The outer group by is on no aggregation. Group by is only needed when an aggregate function is being used. Since no aggregate function, no group by!

Double aggregation (avg(sum(salary)) in subquery not needed Avg, in and of itself, will return an average. Not sure what the sum was attempting to do. if you Avg the sum you'll end up with the sum. (Say I have 1,2,3,4,5: the sum is 15 the avg of the sum is 15. However, the avg is 3.)

And with no GROUP BY, HAVING becomes a WHERE.

SELECT employee_id, last_name, salary
FROM  employees
WHERE salary > (SELECT avg(salary)
                 FROM employees); 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download