Statistical variance functions VAR and VARP – SQL Server
What is the variance
The variance is a measure of how far a set of numbers is spread out. It is one of several descriptors of a probability distribution.
Variance can be calculated from sample or entire population data. In a real world, the variance of a population is not known and must be computed in some ways. So, When dealing with extremely large populations, it is not possible to count every object in the population and a common task is to estimate the variance of a population from a sample.
Formula for computing sample variance:
Variance function in SQL Server
VAR ( [ ALL | DISTINCT ] column name or expression ) – This function is used to compute statistical variance from sample data.
VARP( [ ALL | DISTINCT ] column name or expression ) – This function is used to compute statistical variance for an entire population data.
ALL and DISTINCT are the keywords that are used to select all values or distinct value from the list. These functions return a float value. we should use only numeric data columns for the variance computation and Null values will be ignored from the list.