The STDDEV_SAMP function calculates the sample standard deviation. Only one expression is specified as a parameter. If the DISTINCT or UNIQUE keyword is included, it calculates the sample standard deviation after deleting the duplicates; if the keyword is omitted or is ALL, it calculates the sample standard deviation for all values.
The return value is the same as the square root of the VAR_SAMP Function return value and it is a DOUBLE type. If there are no rows that can be used for calculating a result, NULL will be returned.
The following are the formulas applied to the function.
STDDEV_SAMP = [ { 1 / (N-1) } * SUM( { xI - mean(x) }2 ) ]1/2
STDDEV_SAMP( [ { DISTINCT | DISTINCTROW } | UNIQUE | ALL] expression )
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_SAMP(d) FROM test_table;
stddev_samp(d)
==========================
8.287199825135663e+01
SELECT STDDEV_SAMP(POWER(d,2)+d*2+1) FROM test_table;
stddev_samp( power(d, 2)+d*2+1)
=================================
2.155888498702931e+04
TRUNCATE TABLE test_table;
SELECT STDDEV_SAMP(d) FROM test_table;
stddev_samp(d)
==========================
NULL