Home > SQL Server > Statistical variance functions VAR and VARP – SQL Server

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:

samplevarianceFormula for computing variance of an entire population:

varianceofanentirepopulation

Image source

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.

 Implementation

pic1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: