Product Documentation

Appearance

All platforms

STDDEV_SAMP and STDDEV_POP (window functions) ​

Given a set of integer, decimal, or floating-point numbers, return the sample standard deviation or the population standard deviation, based on a window definition with an optional frame clause. The STDDEV_SAMP function returns a result that is equivalent to the square root of the sample variance of the same set of values. STDDEV_SAMP and STDDEV are synonyms.

STDDEV_SAMP | STDDEV (expression)
STDDEV_POP (expression)
OVER { window_name | ( [ window_definition ] ) }

The expression must be an integer, decimal, or floating-point number. The return type is either DOUBLE PRECISION (for floating-point inputs) or DECIMAL (for all other inputs).

For the full window_definition syntax, see Syntax for Window Functions.

For example:

premdb=# select teamid, avg_att, 
stddev_pop(capacity) over (partition by city order by avg_att rows between 1 preceding and 2 following) as stddevpop 
from team where avg_att>0 order by 1,2;
 teamid | avg_att |  stddevpop   
--------+---------+--------------
     1 |  59.944 |  9298.500000
     2 |  33.690 |  7892.500000
     8 |  11.189 |     0.000000
    13 |  41.500 | 10272.025798
    15 |  24.636 |  4458.995726
    17 |  38.124 |  2260.500000
    22 |  32.201 |     0.000000
    23 |  43.910 |  2260.500000
    24 |  54.041 | 10269.000000
    25 |  75.286 | 10269.000000
    27 |  49.754 |     0.000000
    28 |  26.972 |     0.000000
    36 |  30.751 |     0.000000
    37 |  27.534 |     0.000000
    38 |  43.071 |     0.000000
    39 |  20.711 |     0.000000
    41 |  35.776 | 10101.610623
    42 |  20.594 |     0.000000
    43 |  24.631 |  7892.500000
    44 |  34.910 |  5530.972451
(20 rows)

Note: The STDDEV or STDDEV_SAMP function for an expression that consists of a single value returns NULL. The STDDEV_POP function for an expression that consists of a single value returns 0).