VAR_SAMP Function [Aggregate]

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

Syntax

VAR_SAMP ( [ ALL ] expression )

Parameters

Parameter

Description

expression

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

Returns

DOUBLE

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_SAMP( Quantity ) AS Variance 
FROM SalesOrderItems GROUP BY Year, Quarter 
  ORDER BY Year, Quarter

Year

Quarter

Average

Variance

2000

1

25.775148

205.1158

2000

2

27.050847

227.0939

...

...

...

...

Usage

Note: VAR_SAMP is an alias of VARIANCE.

Computes the sample variance of 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 one less than the number of rows (remaining) in the group or partition.

NULL returns NULL for a one-element input set in Sybase IQ 12.7 and later. In versions earlier than 12.7, NULL returned zero.

Variances are computed according to the following formula, which assumes a normal distribution:


Computes the sample variance of 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 one less than the number of rows remaining in the group or partition

Standards and Compatibility

  • SQL—Vendor extension to ISO/ANSI SQL grammar.

  • Sybase—Not supported by Adaptive Server Enterprise.

Related concepts
Windowing Aggregate Function Usage