SQL Reference - STDDEV

STDDEV(expr)

expr: This represents the population parameter by which all rows are grouped. It is the column or set of columns used to determine the standard deviation. The function calculation is done on these values.

Example

SELECT STDDEV(salary)
FROM employees;

Output

1234.567890123456

Explanation

This statement returns the standard deviation of the salary column from the employees table. The STDDEV() function in MySQL calculates the population standard deviation of a set of numbers. The output is a single decimal value representing the standard deviation.

STDDEV( expression )

expression: A numeric column or expression for which the standard deviation is to be calculated. It represents the set of data points in a PostgreSQL table.

Example

CREATE TABLE student_scores (score INT);
INSERT INTO student_scores (score) VALUES (70), (85), (90), (95), (100);
SELECT STDDEV(score) AS standard_deviation FROM student_scores;

Output

 standard_deviation
---------------------
           11.1803399
(1 row)

Explanation

In this example, we are creating a table named student_scores with column score and inserting some values into it. The STDDEV function is used to return the standard deviation of the values in the score column. In this case, the standard deviation is approximately 11.18.

STDDEV(expr) OVER ([query_partition_clause] [order_by_clause])

expr: This is the column or expression for which we want to calculate the standard deviation.
over: This keyword signifies that STDDEV is going to operate over a set of rows. This is what allows it to be used as an analytical function.
query_partition_clause: This clause divides the result set into partitions (subsets of the data). STDDEV is then calculated for each partition.
order_by_clause: This clause orders the rows in each partition. The standard deviation calculation will then consider the rows in the sequence defined by this order.

Example

SELECT STDDEV(salary) FROM employees;

Output

1700.345623

Explanation

In the above example, the STDDEV() function is used to find the standard deviation of all salary in the employees table. The standard deviation is a statistic that measures the dispersion of a dataset relative to its mean.

STDDEV( [ ALL | DISTINCT ] expression ) OVER ( [ partition_by_clause ] order_by_clause )

all | distinct: This specifies whether each unique value should be considered individually (DISTINCT) or if all values should be taken into account, including duplicates (ALL).
expression: This is the column or set of columns that will be used to calculate the standard deviation.
over clause: This clause defines the window or set of rows for which the standard deviation is computed.
partition_by_clause: This optional clause divides the result set produced by the FROM clause into partitions to which the STDDEV function is applied separately.
order_by_clause: This optional clause defines the logical order of the data within each partition defined by the partition_by_clause. The standard deviation is then computed, based on this specified order.

Example

SELECT STDDEV(points) AS Standard_Deviation
FROM Player;

Output

Standard_Deviation
------------------
15.4530892463161

Explanation

The SQL Server STDDEV function is used to find the standard deviation of the ‘points’ column in the ‘Player’ table. The standard deviation calculated in this example is approximately 15.45. The standard deviation is a measure of how spread out numbers are.