SQL VARIANCE Function

SQL VARIANCE Function returns variance of available records. Oracle VARIANCE Function accepts column/formula as parameter for Variance calculation.

SQL VARIANCE Function Syntax

SELECT VARIANCE(numeric column / formula)
FROM table_name
WHERE conditions;

SQL VARIANCE Function Examples

Suppose we have a table named “Employee” with the data as shown below.

SQL VARIANCE examples are mentioned below

SQL VARIANCE Function – Simple Usage

A simple usage of Oracle VARIANCE Function will be to fetch the variance of a table column.

For example, below SQL VARIANCE query returns variance of salary of employees

SELECT VARIANCE(salary) "Oracle VARIANCE"
FROM employee;

The above Oracle VARIANCE query returns ‘82000000’ as variance of salaries in ‘employee’ table.

Note: We have aliased VARIANCE(salary) as Oracle VARIANCE.

SQL VARIANCE Function – Using Formula Example

Oracle VARIANCE Function accepts formula also as parameter.

For example, the Oracle VARIANCE query returns variance of salary*(commission/100) from employee table.

SELECT salary*(commission/100) "SQL VARIANCE"
FROM employee;

Above Oracle VARIANCE query returns ‘3663333.33333333’ as variance of salary*(commission/100) from employee table.

Note: We have aliased salary*(commission/100) as “SQL VARIANCE”.