SQL Reference - STDDEV

STDDEV_POP(expression)

expression: The set of values, derived from a column or expression, for which the population standard deviation is to be calculated.

Example

SELECT STDDEV_POP(salary) FROM employees;

Output

10500.0

Explanation

In the above example, the STDDEV_POP function calculates the population standard deviation of the salaries for all employees. This means it measures the square root of the variance of the values in the salary column, indicating how spread out these values are. The function returns one single value, the population standard deviation, which in this case is 10500.0.

STDDEV_POP(expression) OVER ([partition_by_clause] [order_by_clause] [frame_clause])

expression: It is the argument on which the population standard deviation STDDEV_POP function operates. This can be a table column name, a derived column expression or any numeric expression.
partition_by_clause: It segments the data into smaller groups or partitions on which the STDDEV_POP function is applied. Data within a partition is arranged in the order specified in the ORDER BY clause.
order_by_clause: It directs the manner in which the data values in the partitions specified by the PARTITION BY clause are sorted. Typically, for the STDDEV_POP function, ordering is ascending and numeric.
frame_clause: Frame clause defines the window frame where the STDDEV_POP function operates. The window is defined as a frame or subset of the current partition depending on the value of “N” rows that precede or follow the current row.

Example

CREATE TABLE test_table (numbers integer);

INSERT INTO test_table (numbers)
VALUES (5), (7), (10), (15), (20), (25);

SELECT STDDEV_POP(numbers)
FROM test_table;

Output

    stddev_pop
-------------------
6.8076923142850886

Explanation

In the SELECT statement, the STDDEV_POP function is used to compute the population standard deviation of the ‘numbers’ column in ‘test_table’. This function incorporates the entire dataset and returns a single value, illustrating the amount of variation or dispersion of a set of values.

STDDEV_POP( expression ) OVER ( [ partition_by_clause ] order_by_clause )

expression: This is a numeric column or the result of a numeric expression for which standard deviation is to be calculated. This can be a column name, constant, function, any combination of these connected by operators, or even a subquery that returns a single value.
partition_by_clause: This optional parameter specifies the columns by which the result set will be partitioned. If specified, the function will compute the standard deviation for each partition separately, as if each partition was a separate result set. It is equivalent to grouping of results based on the column values mentioned in this clause but without actually grouping results.
order_by_clause: This optional parameter specifies the column by which the result set or partition will be ordered. This handles cases where the order of the records is important to the result of the function. However, this clause does not alter the order of the total result set returned by the query. In STDDEV_POP, this clause is typically not utilized as standard deviation is not influenced by order of rows.

Example

SELECT STDDEV_POP(age)
FROM Customer;

Output

8.39

Explanation

The command calculates population standard deviation of the ‘age’ field in the ‘Customer’ table. The output ‘8.39’ suggests the typical deviation of ages from the average age across all customers.

STDDEV_POP(expression) OVER ([query_partition_clause] [order_by_clause] [windowing_clause])

expression: This is the column or set of columns over which the function will be executed. It may include mathematical manipulations, aggregate functions, sub-queries and other SQL elements that can be evaluated to a single value.
query_partition_clause: This optionally divides the data into chunks, with the function applied separately to each chunk. For example, defining this as “partition by department_id” would calculate a separate standard deviation for each department.
order_by_clause: This is an optional component that specifies how to sort the data before the function is applied. The standard deviation will be computed based on the order of rows.
windowing_clause: This option specifies the range of rows used to perform the calculation for the current row. Windowing clause is specified by RANGE or ROWS keywords followed by specification that configures the window. The specification might be UNBOUNDED PRECEDING, N PRECEDING, CURRENT ROW, BETWEEN, UNBOUNDED FOLLOWING, or N FOLLOWING.

Example

SELECT STDDEV_POP(salary) FROM employees;

Output

STDDEV_POP(SALARY)
------------------
     2500.00

Explanation

The SQL statement uses the SQL function STDDEV_POP to find the population standard deviation of all salaries in the employees table. The result of 2500.00 shows the square root of the average squared deviation of salary values from their average value.