SQL HAVING clause with aggregate fuctions?

Question

I am trying to find the employees of company that have salary bigger than the average salary of all the employees. I would like to point out from the start that I don't want the average salary to be present in the final result, so I ommit it in the SELECT statement. These are the things I've tried:

SELECT employee.lastname,employee.firstname,employee.salary FROM employee
GROUP BY employee.salary
HAVING employee.salary > avg(employee.salary);

This results in an empty result table

However the following surprisingly returns all the employees of the company despite the '=' symbol.

SELECT employee.lastname,employee.firstname,employee.salary FROM employee
GROUP BY employee.salary
HAVING employee.salary = avg(employee.salary);

This returns empty table again:

SELECT employee.lastname,employee.firstname,employee.salary FROM employee
WHERE (SELECT avg(employee.salary) FROM employee 
GROUP BY employee.salary
HAVING employee.salary > AVG(employee.salary));

So to conclude this post I would appreciate some insight about the right use of HAVING with an aggregate function, some insight about the reason that the snippets result in an empty table.

Answer 1

When you GROUP BY employee.salary then the average salary of each group is equal to employee.salary because all the salaries of the group are equal.
So the condition:

employee.salary > avg(employee.salary)

is always FALSE and you get no rows, and the condition:

employee.salary = avg(employee.salary)

is always TRUE and the result is to get all the groups returned.
The correct code to get what you want is:

SELECT employee.lastname, employee.firstname, employee.salary 
FROM employee
WHERE employee.salary > (SELECT avg(employee.salary) FROM employee);

Answer 2

Remove the starting open bracket ( before avg(.. and last closing bracket ) before the semicolon as you have misplaced the brackets leading to syntax error

    SELECT  employee.lastname,
  employee.firstname,employee.salary 
   FROM employee
   WHERE employee.salary > 
( SELECT avg(employee.salary) FROM 
   employee);

Answer 3

Try this

SELECT lastname, firstname, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee)
ORDER BY salary DESC

The sub-query for the average doesn't need a GROUP BY when only an aggregate function is used in the SELECT or HAVING clause.

Or to use something more fancy:

SELECT lastname, firstname, salary
FROM
(
    SELECT lastname, firstname, salary
    , AVG(salary) OVER () AS avg_salary
    FROM employee
) q
WHERE salary > avg_salary

Answer 4

You can deal with two sets/tables, one record-level and the other aggregated, even if they're the same set:

select e.lastname , e.firstname , e.salary 
FROM employee e, (
    select avg(a.salary) avg_salary
    from employee a
) av
where 1=1
  and e.salary > av.avg_salary
;

Answer 5

You have aggregated by employee.salary. So, in this query:

HAVING employee.salary > avg(employee.salary);

Each row before the HAVING has exactly one salary value. The average of a single value -- no matter how many are in the group -- is that value. Because a value cannot be bigger than itself, no rows are returned.

This clause:

 HAVING employee.salary = avg(employee.salary);

is exactly the same thing, except all rows with non-NULL salaries match this condition. Hence, all rows are returned.

As others have mentioned, the more typical solution is a subquery:

select e.*
from employee e
where e.salary > (select avg(e2.salary) from employee e2);

Note the use of table aliases. These are highly recommended.

A more modern solution would use window functions:

select . . .   -- select the columns you want
from (select e.*, avg(e.salary) over () as avg_salary
      from employee e
     ) e
where e.salary > avg_salary;