Find values above the average

How to find values in a table column that are above (or below) the average of that column.

Finding a value that is above (or below) the average is very easy. However, the devil can be in the details.

Finding values above the average with a subquery

To find a column average:

SELECT avg(salary) FROM employee;

To find a value that is above the average, we can use a subquery, or nested query:

SELECT name, surname, salary
    FROM employee
    WHERE salary > (
        SELECT avg(salary) FROM employee
    )
;

Find values that are well above the average

But suppose that a value that is slightly above the average is not interesting for you. Maybe the average is 100, and there are many 101 values, but you don’t want to see them.

The simplest way is to use a fixed margin, for example 1000:

SELECT name, surname, salary
    FROM employee
    WHERE (salary - 1000) > (
        SELECT avg(salary) FROM employee
    )
;

But maybe you don’t want a fixed value, you want something smarter. Maybe you want to exclude values that are higher than the average plus the standard deviation.

Any number of values (zero or all of them) diverge from the average. The standard deviation can be defined as the average of the difference between real values and their average. In SQL, the stddev() aggregate function returns the standard deviation.

SELECT name, surname, salary
    FROM employee
    WHERE (salary - (
        SELECT stddev(salary) FROM employee
    )) > (
        SELECT avg(salary) FROM employee
    )
;

Finding values above the average with a subquery

Find values that are well above the average

Reference