syntax and example of variance function in oracle plsql

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.

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

---