Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

STDDEV/STDDEV_POP Functions

Description

The functions STDDEV and STDDEV_POP are used interchangeably and they return a standard deviation of the values calculated for all rows. The STDDEV_POP function is a standard of the SQL:1999. Only one expression is specified as a parameter. If the DISTINCT or UNIQUE keyword is inserted before the expression, they calculate the standard deviation after deleting duplicates; if keyword is omitted or ALL, they it calculate the standard deviation for all values.

The return value is the same with the square root of it's variance (the return value of VAR_POP Function) and it is a DOUBLE type. If there are no rows that can be used for calculating a result, NULL is returned.

The following is a formula that is applied to the function.

STDDEV_POP = [ (1/N) * SUM( { xI - AVG(x) }2 ) ]1/2

  • SUM: Sum
  • AVG: Average

Warning In CUBRID 2008 R3.1 or earlier, the STDDEV function worked the same as the STDDEV_SAMP Function.

Syntaxs

STDDEV_POP( [ { DISTINCT | DISTINCTROW } | UNIQUE | ALL] expression )

  • expression: Specifies an expression that returns a numeric value.
  • ALL: Calculates the standard deviation for all data (default).
  • DISTINCT or UNIQUE: Calculates the standard deviation without duplicates.
Example

CREATE TABLE test_table (d DOUBLE);

INSERT INTO test_table VALUES(78), (63.65), (230.54), (32), (17.2), (195.7689), (57.57);

 

SELECT STDDEV_POP(d) FROM test_table;

             stddev_pop(d)

==========================

    7.672456168942171e+01

 

SELECT STDDEV_POP(POWER(d,2)+d*2+1) FROM test_table;

  stddev_pop( power(d, 2)+d*2+1)

================================

          1.995964904967644e+04

 

TRUNCATE TABLE test_table;

SELECT STDDEV_POP(d) FROM test_table;

                stddev_pop(d)

==========================

                      NULL