Oracle VARIANCE Function

The variance is the average squared distance of values from their mean. It is the basis of the standard deviation and of many statistical tests, and it adds up across independent sources of variation, which the standard deviation does not. Oracle provides VARIANCE, VAR_SAMP, and VAR_POP.

Code for This Guide

The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

variance([distinct | all] expr)
var_samp(expr)
var_pop(expr)

VAR_POP divides the sum of squared deviations by n, for a whole population; VAR_SAMP by n - 1, for a sample. VARIANCE is the sample version, except that it returns 0 rather than NULL for a single row.

Three Variants

Example:

select round(variance(salary))     as variance,
       round(var_pop(salary))      as var_pop,
       round(var_samp(salary))     as var_samp,
       round(stddev(salary), 1)    as stddev,
       round(stddev_pop(salary), 1)  as stddev_pop,
       round(stddev_samp(salary), 1) as stddev_samp
from   employees
where  department_id = 30;

Output:

   VARIANCE     VAR_POP    VAR_SAMP    STDDEV    STDDEV_POP    STDDEV_SAMP
___________ ___________ ___________ _________ _____________ ______________
   22622909    20566281    22622909    4756.4          4535         4756.4

VARIANCE and VAR_SAMP agree at 22,622,909; VAR_POP is smaller at 20,566,281. The unit is the square of the data's unit, salary squared, which is why the numbers are so large and why the standard deviation, its square root, is easier to read.

Variance per Group

Example:

select department_id, count(*) as staff, round(avg(salary)) as avg_salary,
       round(stddev(salary)) as stddev_salary, round(variance(salary)) as variance_salary
from   employees
where  department_id in (20, 30, 40, 50)
group  by department_id
order  by department_id;

Output:

   DEPARTMENT_ID    STAFF    AVG_SALARY    STDDEV_SALARY    VARIANCE_SALARY
________________ ________ _____________ ________________ __________________
              20       12         17817             5155           26570606
              30       11          9009             4756           22622909
              40       10         14210             8158           66547667
              50        4         16400            11543          133246667

Department 50's variance of 133 million is five times that of department 20, reflecting much more uneven salaries.

A Single Row

Example:

-- with a single row, VARIANCE and STDDEV return 0, the _SAMP versions NULL
select variance(salary) as variance, var_samp(salary) as var_samp,
       stddev(salary) as stddev, stddev_samp(salary) as stddev_samp
from   employees
where  employee_id = 100;

Output:

   VARIANCE    VAR_SAMP    STDDEV    STDDEV_SAMP
___________ ___________ _________ ______________
          0                     0

VARIANCE returns 0 and VAR_SAMP NULL for one row.

Things to Know

• Use VAR_POP when the rows are the complete population, such as all employees; VAR_SAMP when they are a sample.
• Variance is used by STATS_F_TEST to test whether two groups vary equally.
• All variants ignore NULLs and work as analytic functions with OVER.

Related Guides

• Oracle STDDEV Function

Conclusion

VARIANCE, VAR_SAMP, and VAR_POP measure the spread of a group as the mean squared deviation. Choose the population or sample version to match your data, and take the square root, the standard deviation, to report the spread in the data's own unit.