SQL VARIANCE Function

Oracle VARIANCE FunctionSQL 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.

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

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”.


Leave a Reply

Your email address will not be published. Required fields are marked *