Neoview SQL Reference Manual (R2.4)

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 a Neoview SQL extension.
STDDEV ([ALL | DISTINCT] expression [,weight])
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” (page 475).
Because the definition of variance has N-1 in the denominator of the expression (if weight is not
specified), Neoview SQL 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.
Data Type of the Result
The data type of the result is always DOUBLE PRECISION.
Operands of the Expression
The expression includes columns from the rows of the SELECT result table but cannot include
an aggregate function. These are valid:
STDDEV (SALARY)
STDDEV (SALARY * 1.1)
STDDEV (PARTCOST * QTY_ORDERED)
Nulls
STDDEV is evaluated after eliminating all nulls from the set. If the result table is empty, STDDEV
returns NULL.
FLOAT(54) and DOUBLE PRECISION Data
Avoid using large FLOAT(54) or DOUBLE PRECISION values as arguments to STDDEV. If
SUM(x * x) exceeds the value of 1.15792089237316192e77 during the computation of STDDEV(x),
a numeric overflow occurs.
452 SQL Functions and Expressions