STDDEV aggregate function

The STDDEV function returns the biased standard deviation (division by n) of a set of numbers.

The schema is SYSIBM.

expression: An expression that returns a value of any built-in numeric data type.

If the argument is DECFLOAT(n), the result is DECFLOAT(n); otherwise, the result is double-precision floating-point. The result can be null.

The function is applied to the set of values derived from the argument values by the elimination of null values. If DISTINCT is specified, redundant duplicate values are eliminated. When interpreting the DISTINCT clause for decimal floating-point values that are numerically equal, the number of significant digits in the value is not considered. For example, the decimal floating-point number 123.00 is not distinct from the decimal floating-point number 123. The representation of the number returned from the query will be any one of the representations encountered (for example, either 123.00 or 123).

If the function is applied to an empty set, the result is a null value. Otherwise, the result is the standard deviation of the values in the set.

The calculation that is used to determine the biased standard deviation is logically equivalent to the following formula:

   STDDEV = SQRT(VARIANCE(expression))

where SQRT(VARIANCE(expression)) is the square root of the biased variance.

The order in which the values are aggregated is undefined, but every intermediate result must be within the range of the result data type.

STDDEV_POP can be specified in place of STDDEV.

Example

Set the host variable DEV to the standard deviation of the salaries of employees in department 'A00' in the EMPLOYEE table. The data type for the host variable DEV is double-precision floating point.

   SELECT STDDEV(SALARY)
     INTO :DEV
     FROM EMPLOYEE
     WHERE WORKDEPT = 'A00'

DEV is set to a number with an approximate value of 9938.00.

The following result set is shown for reference.

   SELECT SALARY FROM EMPLOYEE WHERE WORKDEPT = 'A00'

SALARY     
-----------
  152750.00
   66500.00
   49250.00
   46500.00
   39250.00

  5 record(s) selected.