STDDEV_POP or STDDEV
The STDDEV_POP function returns the biased standard deviation (/n) of a set of numbers.
The formula used to calculate the biased standard deviation is:
STDDEV_POP = SQRT(VAR_POP)
where SQRT(VAR_POP) is the square root of the variance.
- numeric-expression
- An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. If the argument is a character-string or graphic-string, it is cast to DECFLOAT(34) before evaluating the function.
If the argument is DECFLOAT(n), the result of the function is DECFLOAT(34). Otherwise, the data type of the result is double-precision floating point.
The function is applied to the set of values derived from the argument values by the elimination of null values. If DISTINCT is specified, duplicate values are eliminated.
The result can be null. If the set of values is empty, the result is a null value. Otherwise, the result is the standard deviation of the values in the set.
The order in which the values are added is undefined, but every intermediate result must be within the range of the result data type.
Notes
Results involving DECFLOAT special values: If the data type of the argument is decimal floating-point and a special value of sNaN or -sNaN, or both +Infinity and -Infinity are included in the aggregation, an error or warning is returned. Otherwise, if +NaN or -NaN is found, the result is +NaN or -NaN. If +Infinity or -Infinity is found, the result is +Infinity or -Infinity.
Syntax alternatives: STDEV_POP should be used for conformance to the SQL 2003 standard.
Example
- Using the EMPLOYEE table, set the host variable DEV (double-precision
floating point) to the standard deviation of the salaries for those
employees in department A00.
Results in DEV being set to approximately 9742.43.SELECT STDDEV_POP(SALARY) INTO :DEV FROM EMPLOYEE WHERE WORKDEPT = 'A00';