Producing NULL Value related to AVG(field) in HAVING CLAUSE

Question

Here is my table:

There are Multiple Salaries under 61,000.

The AVG Salary for Related table is 61K.

If I write a query:

SELECT `StaffID`, `Title`, `FirstName`, `LastName`, `DeptID`, `Salary` 
FROM `Staff` 
WHERE `Left` < '2018-00-00' 
AND `ContractType` = 'Full time' *HAVING AVG(`Salary`) < `Salary`*;

It will produce one result with the Salary being 27000. The AVG Salary at 61K is not less than 27K.

If I change the comparison operator in the same query to:

*AVG(`Salary`) > `Salary`;*

No record will be produced.

Can anyone shine a light as to why?

Answer 1

This query is malformed:

SELECT StaffID, Title, FirstName, LastName, DeptID, Salary
FROM Staff
WHERE Left < '2018-00-00' AND ContractType = 'Full time'
HAVING AVG(Salary) < Salary;

It is an aggregation query because it has an aggregation function AVG(). However, there is no GROUP BY, so there should be no unaggregated columns in the SELECT. That is, the query should generate an error.

I can only assume you are using a database that -- sadly -- allows such malformed queries.

You don't state what you want to do. In particular, do the where conditions apply to the average or not. However, one good solution uses window functions:

SELECT StaffID, Title, FirstName, LastName, DeptID, Salary
FROM (SELECT s.*, AVG(Salary) OVER () as avg_salary
      FROM Staff s
      WHERE Left < '2018-00-00' AND ContractType = 'Full time'
     ) s
WHERE salary > avg_salary;

Answer 2

Sub-query Worked:

SELECT StaffID, Title, FirstName, LastName, DeptID, Salary FROM Staff WHERE Left < '2018-00-00' 
AND ContractType = 'Full time' HAVING 'Salary' < 
(
    Select AVG(Salary) FROM Staff
);