STDDEV/STDDEV_POP Functions

Description

The functions STDDEV and STDDEV_POP are used interchangeably and they return a standard variance 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 sample standard variance after deleting duplicates; if keyword is omitted or ALL, they it calculate the sample standard variance 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( { x_I - AVG(x) }² ) ]^1/2

• SUM: Sum
• AVG: Average

For analytic function examples, see the following analytic function examples. For more information, see Overview.

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

Syntax

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

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

Example of Aggregate Function

The following example shows how to output the population standard variance of all students for all subjects.

CREATE TABLE student (name VARCHAR(32), subjects_id INT, score DOUBLE);

INSERT INTO student VALUES

('Jane',1, 78),

('Jane',2, 50),

('Jane',3, 60),

('Bruce', 1, 63),

('Bruce', 2, 50),

('Bruce', 3, 80),

('Lee', 1, 85),

('Lee', 2, 88),

('Lee', 3, 93),

('Wane', 1, 32),

('Wane', 2, 42),

('Wane', 3, 99),

('Sara', 1, 17),

('Sara', 2, 55),

('Sara', 3, 43);

SELECT STDDEV_POP(score) FROM student;

         stddev_pop(score)

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

     2.329711474744362e+01

Example of Analytic Function

The following example shows how to output the score and population standard variance of all students by subject (subjects_id).

SELECT subjects_id, name, score, STDDEV_POP(score) OVER(PARTITION BY subjects_id) std_pop FROM student ORDER BY subjects_id, name;

  subjects_id  name                                     score                   std_pop

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

            1  'Bruce'                  6.300000000000000e+01     2.632869157402243e+01

            1  'Jane'                   7.800000000000000e+01     2.632869157402243e+01

            1  'Lee'                    8.500000000000000e+01     2.632869157402243e+01

            1  'Sara'                   1.700000000000000e+01     2.632869157402243e+01

            1  'Wane'                   3.200000000000000e+01     2.632869157402243e+01

            2  'Bruce'                  5.000000000000000e+01     1.604992211819110e+01

            2  'Jane'                   5.000000000000000e+01     1.604992211819110e+01

            2  'Lee'                    8.800000000000000e+01     1.604992211819110e+01

            2  'Sara'                   5.500000000000000e+01     1.604992211819110e+01

            2  'Wane'                   4.200000000000000e+01     1.604992211819110e+01

            3  'Bruce'                  8.000000000000000e+01     2.085185843036539e+01

            3  'Jane'                   6.000000000000000e+01     2.085185843036539e+01

            3  'Lee'                    9.300000000000000e+01     2.085185843036539e+01

            3  'Sara'                   4.300000000000000e+01     2.085185843036539e+01

            3  'Wane'                   9.900000000000000e+01     2.085185843036539e+01