SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-166
VARIANCE Function
VARIANCE Function
Considerations for VARIANCE
Examples of VARIANCE
VARIANCE is an aggregate function that returns the statistical variance of a set of
numbers.
VARIANCE is an SQL/MX extension.
ALL | DISTINCT
specifies whether duplicate values are included in the computation of the
VARIANCE of the expression. The default option is ALL, which causes duplicate
values to be included. If you specify DISTINCT, duplicate values are eliminated
before the VARIANCE function is applied. If DISTINCT is specified, you cannot
specify weight.
expression
specifies a numeric value expression that determines the values for which to
compute the variance. expression cannot contain an aggregate function or a
subquery. The DISTINCT clause specifies that the VARIANCE function operates
on distinct values from the one-column table derived from the evaluation of
expression.
weight
specifies a numeric value expression that determines the weights of the values for
which to compute the variance. weight cannot contain an aggregate function or a
subquery. weight is defined on the same table as expression. The one-column
table derived from the evaluation of expression and the one-column table
derived from the evaluation of weight must have the same cardinality.
Considerations for VARIANCE
Definition of VARIANCE
Suppose that v
i
are the values in the one-column table derived from the evaluation of
expression. N is the cardinality of this one-column table that is the result of applying
the expression to each row of the source table and eliminating rows that are null.
If weight is specified, w
i
are the values derived from the evaluation of weight. N is
the cardinality of the two-column table that is the result of applying the expression
and weight to each row of the source table and eliminating rows that have nulls in
either column.
VARIANCE ([ALL | DISTINCT] expression [,weight])