The VAR() aggregate function computes the statistical variance of a set of numbers. Variance measures how spread out a set of numbers is from their average value. A low variance indicates that the data points tend to be very close to the mean (also called the expected value) of the set, while a high variance indicates that the data points are spread out over a wider range of values.
The general syntax for the VAR() function is:
VAR(expression) [ OVER ( [ partition_clause ] order_clause ) ]| Parameter | Description |
|---|---|
expression |
An integer, float, or money-type numeric expression that returns a value from the set of numbers over which the variance is to be calculated. It cannot be a column of a large object (LOB) data type, or a bit, image, text, ntext, xml, sql_variant, geography, or geometry data type. |
The VAR() function returns the variance of the specified expression. The return type depends on the input expression's data type. For example, if the input is an integer, the output might be a float.
VAR() function calculates the sample variance.VAR() returns NULL.OVER clause allows the function to be used as a window function, enabling calculations across a set of table rows that are somehow related to the current row.This example calculates the variance of salaries in an 'Employees' table.
SELECT VAR(Salary) AS SalaryVariance
FROM Employees;This example calculates the variance of salaries for each department, showing the variance relative to the entire dataset and also partitioned by department.
SELECT
EmployeeName,
Department,
Salary,
VAR(Salary) OVER () AS OverallSalaryVariance,
VAR(Salary) OVER (PARTITION BY Department) AS DepartmentSalaryVariance
FROM Employees;