ORACLE VARIANCE

In this article, we will learn to use VARIANCE function in Oracle.

Just like STDDEV function, VARIANCE can be used both as an aggregate and analytical function.

Variance in statistics tells us how far a set of values is spread out from their average or mean value.

Let’s see some examples to understand this better:

VARIANCE as an aggregate function

Syntax

SELECT VARIANCE( [ DISTINCT | ALL ] expression )
FROM TABLE;

Here,

expression – It could be a set of values or column name.

Using DISTINCT and ALL clause is optional. We use DISTINCT when we want to use this aggregate function only for the unique values.

We use the following query to calculate the variance in salary in each department. We use ROUND function to round off the calculate value to 2 digits after decimal.

SELECT department_id, 
ROUND(VARIANCE(salary),2) AS variance_salary
FROM employees
GROUP BY department_id
ORDER BY department_id;

Result:

The query calculates the variance in salary in each department.

VARIANCE as an analytical function

We use the following query to calculate cumulative variance of salary in department_id=100 and we order the data by hire date.

SELECT first_name, last_name, hire_date, salary,
VARIANCE(salary) OVER (ORDER BY hire_date) "Variance"
FROM employees
WHERE department_id = 100;

Result:

In this tutorial, we learned how to use VARIANCE function both as an aggregate and analytical function.