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

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-142

STDDEV Function

Considerations for STDDEV

Examples of STDDEV

STDDEV is an aggregate function that returns the standard deviation of a set of

numbers.

STDDEV is an SQL/MX extension.

ALL | DISTINCT

specifies whether duplicate values are included in the computation of the STDDEV

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

STDDEV 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 standard deviation. The expression cannot contain an aggregate

function or a subquery. The DISTINCT clause specifies that the STDDEV 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 standard deviation. 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 STDDEV

Definition of STDDEV

The standard deviation of a value expression is defined to be the square root of the

variance of the expression. See VARIANCE Function on page 9-166.

Because the definition of variance has N-1 in the denominator of the expression (if

weight is not specified), NonStop SQL/MX returns a system-defined default setting of

zero (and no error) if the number of rows in the table, or a group of the table, is equal

to 1.

STDDEV ([ALL | DISTINCT] expression [,weight])