STDDEV_SAMP Function

Description

The STDDEV_SAMP function calculates the sample standard variance. Only one expression is specified as a parameter. If the DISTINCT or UNIQUE keyword is inserted before the expression, it calculates the sample standard variance after deleting duplicates; if a keyword is omitted or ALL, it calculates the sample standard variance for all values.

The return value is the same as the square root of it's sample variance (VAR_SAMP 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 are the formulas applied to the function.

STDDEV_SAMP = [ { 1 / (N-1) } * SUM( { x_I - mean(x) }² ) ]^1/2

• SUM: Sum
• mean: Sample mean

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

Syntax

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

• expression: An expression that returns a numeric value
• ALL: Used to calculate the standard variance for all values. It is the default value.
• DISTINCT or UNIQUE: Used used to calculate the standard variance for the unique values without duplicates.

Example of Aggregate Function

The following example shows how to output the sample 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_SAMP(score) FROM student;

        stddev_samp(score)

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

     2.411480477888654e+01

Example of Analytic Function

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

SELECT subjects_id, name, score, STDDEV_SAMP(score) OVER(PARTITION BY subjects_id) std_samp FROM student ORDER BY subjects_id, name;

  subjects_id  name                                     score                  std_samp

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

            1  'Bruce'                  6.300000000000000e+01     2.943637205907005e+01

            1  'Jane'                   7.800000000000000e+01     2.943637205907005e+01

            1  'Lee'                    8.500000000000000e+01     2.943637205907005e+01

            1  'Sara'                   1.700000000000000e+01     2.943637205907005e+01

            1  'Wane'                   3.200000000000000e+01     2.943637205907005e+01

            2  'Bruce'                  5.000000000000000e+01     1.794435844492636e+01

            2  'Jane'                   5.000000000000000e+01     1.794435844492636e+01

            2  'Lee'                    8.800000000000000e+01     1.794435844492636e+01

            2  'Sara'                   5.500000000000000e+01     1.794435844492636e+01

            2  'Wane'                   4.200000000000000e+01     1.794435844492636e+01

            3  'Bruce'                  8.000000000000000e+01     2.331308645374953e+01

            3  'Jane'                   6.000000000000000e+01     2.331308645374953e+01

            3  'Lee'                    9.300000000000000e+01     2.331308645374953e+01

            3  'Sara'                   4.300000000000000e+01     2.331308645374953e+01

            3  'Wane'                   9.900000000000000e+01     2.331308645374953e+01