Categories:
Aggregate Functions (General) , Window Functions

VAR_SAMP

Returns the sample variance of non-NULL records in a group. If all records inside a group are NULL, a NULL is returned.

Aliases:
VARIANCE , VARIANCE_SAMP

Syntax

VAR_SAMP( [DISTINCT] <expr> )

Arguments

expr
The expr should evaluate to one of the numeric data types.

Returns

The data type of the returned value is NUMBER(<precision>, <scale>). The scale depends upon the values being processed.

Usage Notes

  • For single-record inputs, VAR_SAMP, VARIANCE, and VARIANCE_SAMP all return NULL. This is different from the Oracle behavior, where VAR_SAMP returns NULL for a single record and VARIANCE returns 0.

Examples

This example shows how to use the VAR_SAMP function:

Create and fill a table:

create table aggr(k int, v decimal(10,2), v2 decimal(10, 2));
insert into aggr values 
   (1, 10, null),
   (2, 10, 11), 
   (2, 20, 22), 
   (2, 25, null), 
   (2, 30, 35);

Query the table, calling VAR_SAMP():

SELECT k, var_samp(v), var_samp(v2) 
    FROM aggr 
    GROUP BY k;
+---+---------------+----------------+
| K |   VAR_SAMP(V) |   VAR_SAMP(V2) |
|---+---------------+----------------|
| 1 |          NULL |           NULL |
| 2 | 72.9166666667 | 144.3333333333 |
+---+---------------+----------------+