Categories:

Aggregate Functions (General) , Window Functions (General, Window frame)

# STDDEV¶

Returns the sample standard deviation (square root of sample variance) of non-NULL values. If all records inside a group are NULL, returns NULL.

Aliases:

STDDEV_SAMP

## Syntax¶

Aggregate function

STDDEV( [ DISTINCT ] <expression_1> )


Window function

STDDEV( [ DISTINCT ] <expression_1> ) OVER (
[ PARTITION BY <expression_2> ]
[ ORDER BY <expression_3> [ ASC | DESC ] [ <window_frame> ] ]
)


For details about window_frame syntax, see Window Frame Syntax and Usage.

## Arguments¶

expression_1

An expression that evaluates to a numeric value. This is the expression on which the standard deviation is calculated.

expression_2

This is the optional expression to partition by.

expression_3

This is the optional expression to order by within each partition.

## Returns¶

The data type of the returned value is DOUBLE.

## Usage Notes¶

• For single-record inputs, STDDEV_SAMP and STDDEV both return NULL. This is different from the Oracle behavior, where STDDEV_SAMP returns NULL for a single record and STDDEV returns 0.

• When passed a VARCHAR expression, this function implicitly casts the input to floating point values. If the cast cannot be performed, an error is returned.

• When this function is called as a window function (i.e. with an OVER clause):

• If the OVER clause contains an ORDER BY subclause, then:

• A window frame is required. If no window frame is specified explicitly, then the ORDER BY implies a cumulative window frame:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

For information about window frames, including syntax and examples, see Window Frame Syntax and Usage.

• Using the keyword DISTINCT inside the window function is prohibited and results in a compile-time error.

## Examples¶

This shows the standard deviation of a small sample of integers:

CREATE TABLE devious (i INTEGER);
INSERT INTO devious (i) VALUES
(6),
(10),
(14)
;

SELECT STDDEV(i) FROM devious;


Output:

+----------+
| STDDEV() |
|----------|
|        4 |
+----------+