All Products
Search
Document Center

STDDEV_SAMP

Last Updated: Jun 18, 2021

The STDDEV_SAMP function calculates the sample standard deviation. The STDDEV_SAMP function uses numeric data as arguments and returns numeric data. The difference between this function and the STDDEV function is that if only one row of input data is available, STDDEV returns 0 but STDDEV_SAMP returns NULL.

Note

The sample standard deviation is the square root of a sample variance.

Syntax

STDDEV_SAMP([ALL] expr) [ OVER (analytic_clause) ]

If the function is used as an analytic function, you must use the full syntax of a window function. The function calculates a set of rows and returns multiple values. If the function is used as an aggregate function, the function aggregates a set of rows and returns only one value. In this case, you do not need to add the OVER keyword.

Parameters

Parameter

Description

ALL

All the numeric columns.

expr

The expression of the numeric types (NUMBER, FLOAT, BINARY_FLOAT, and BINARY_DOUBLE) or the data types that can be converted to the numeric types.

OVER

Uses the OVER clause to define a window for calculation.

Return type

The return type is the same as the data type of the expr parameter.

Examples

Examples of the analytic function

The following statements create the employees table and insert data into the table:

CREATE TABLE employees (manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000);      
INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000);     
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(100, 'Weiss',  '2019-07-11',13500);     
INSERT INTO employees VALUES(100, 'Russell', '2019-10-05', 13000);
INSERT INTO employees VALUES(100, 'Partners',  '2018-12-01',14000);     
INSERT INTO employees VALUES(200, 'Ross',  '2019-06-11',13500);     
INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 13000);
INSERT INTO employees VALUES(200, 'Part',  '2018-08-11',14000);
COMMIT;

Call the function and execute the following statement:

SELECT manager_id, last_name, hiredate, salary,STDDEV_SAMP(salary) OVER (PARTITION BY manager_id
ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev 
FROM employees ORDER BY manager_id, last_name, hiredate, salary, cum_sdev;

The following result is returned:

+------------+-----------+------------+--------+-------------------------------------------+
| MANAGER_ID | LAST_NAME | HIREDATE   | SALARY | CUM_SDEV                                  |
+------------+-----------+------------+--------+-------------------------------------------+
|        100 | De Haan   | 2018-05-01 |  11000 | 4702.127178203498995615489088200868644482 |
|        100 | Errazuriz | 2017-07-21 |   1400 |  212.132034355964257320253308631454711785 |
|        100 | Hartstein | 2019-05-01 |  14000 | 6340.346993658943269176828928801701088079 |
|        100 | Partners  | 2018-12-01 |  14000 | 6064.899009876421676804205219406952308814 |
|        100 | Raphaely  | 2017-07-01 |   1700 |                                      NULL |
|        100 | Raphaely  | 2017-07-22 |   1700 |  173.205080756887729352744634150587236694 |
|        100 | Russell   | 2019-10-05 |  13000 | 6026.474330580265330900400184969999384459 |
|        100 | Weiss     | 2019-07-11 |  13500 | 6244.311697171159907069428668980211861012 |
|        200 | Bell      | 2019-05-25 |  13000 |  707.106781186547524400844362104849039285 |
|        200 | Part      | 2018-08-11 |  14000 |                                      NULL |
|        200 | Ross      | 2019-06-11 |  13500 |                                       500 |
+------------+-----------+------------+--------+-------------------------------------------+

Examples of the aggregate function

Call the function and execute the following statement:

SELECT STDDEV_SAMP(salary) FROM employees ;

The following query result is returned:

+-------------------------------------------+
| STDDEV_SAMP(SALARY)                       |
+-------------------------------------------+
| 5506.194858355615640082358245403620332764 |
+-------------------------------------------+