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] <expr> )


Window function

STDDEV( <expr1> ) OVER (
[ PARTITION BY <expr2> ]
[ ORDER BY <expr3> [ ASC | DESC ] [ <window_frame> ] ]
)


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

## Arguments¶

expr1

An expression that evaluates to a numeric value.

expr2

This is the optional expression to partition by.

expr3

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 called as a window function:

• The keyword DISTINCT is permitted syntactically, but is ignored.

• If an ORDER BY sub-clause is used inside the OVER clause, then a window frame must be used. If no window frame is specified, then the ORDER BY implies a cumulative window frame:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

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

## 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 |
+----------+