VAR_POP function [Aggregate]

Function

Computes the statistical variance of a population consisting of a numeric-expression, as a DOUBLE.

Syntax

VAR_POP ( [ ALL ] expression )

Parameters

expression The expression (commonly a column name) whose population-based variance is calculated over a set of rows.

Examples

The following statement lists the average and variance in the number of items per order in different time periods:

SELECT year( ShipDate ) AS Year, quarter( ShipDate )
  AS Quarter, AVG( Quantity ) AS Average, 
  VAR_POP( Quantity ) AS Variance 
FROM SalesOrderItems GROUP BY Year, Quarter 
  ORDER BY Year, Quarter

Year

Quarter

Average

Variance

2000

1

25.775148

203.9021

2000

2

27.050847

225.8109

...

...

...

...

Usage

Computes the population variance of the provided value expression evaluated for each row of the group or partition (if DISTINCT was specified, then each row that remains after duplicates have been eliminated), defined as the sum of squares of the difference of value expression, from the mean of value expression, divided by the number of rows (remaining) in the group or partition.

Population-based variances are computed according to the following formula:

Computes the population variance of the provided value expression evaluated for each row of the group or partition if DISTINCT was specified, then each row that remains after duplicates have been eliminated, defined as the sum of squares of the difference of value expression, from the mean of value expression, divided by the number of rows remaining in the group or partition

Standards and compatibility

See also

“Analytical functions”

Chapter 2, “Using OLAP” in the System Administration Guide: Volume 2