MySQL Standard Deviation Functions

MySQL standard deviation functions

MySQL makes it easy for you to calculate the population standard deviation and sample standard deviation.

To calculate population standard deviation, you use one of the following functions:

• STD(expression) – returns the population standard deviation of the expression. The STD function returns NULL if there is no matching row.
• STDDEV(expression) – is equivalent to the STD function. It is provided to be compatible with Oracle Database only.
• STDEV_POP(expression) – is equivalent to the STD function.

To calculate the sample standard deviation, you use the STDDEV_SAMP (expression)function.

MySQL also provides some functions for population variance and sample variance calculation:

• VAR_POP(expression) – calculates the population standard variance of the expression.
• VARIANCE(expression) – is equivalent to the VAR_POP function.
• VAR_SAMP(expression) – calculates the sample standard variance of the expression.

Examples of MySQL standard deviation functions

Let’s take a look at the orders table in the sample database.

Examples of population standard deviation functions

First, the following query returns the customer numbers and the number of orders from the orders table:

SELECT customerNumber,
       COUNT(*) orderCount
FROM orders
WHERE status = 'Shipped'
GROUP BY customerNumber;Code language: SQL (Structured Query Language) (sql)

Second, the following statement calculates the population standard deviation of the number of orders of the customers:

SELECT FORMAT(STD(orderCount),2)
FROM (SELECT customerNumber, count(*) orderCount
FROM orders
GROUP BY customerNumber) t;Code language: SQL (Structured Query Language) (sql)

Try It Out

Notice that the FORMAT function is used for formatting the result of the STD function.

Examples of sample standard deviation functions

Suppose you only want to evaluate shipped orders in the orders table.

First, the following query returns the customer numbers and the number of shipped orders:

SELECT customerNumber, count(*) orderCount
FROM orders
WHERE status = 'Shipped'
GROUP BY customerNumber;Code language: SQL (Structured Query Language) (sql)

Second, the following query uses the STDDEV_SAMP function to calculate the sample standard deviation:

SELECT FORMAT(STDDEV_SAMP(orderCount),2)
FROM (SELECT customerNumber, count(*) orderCount
FROM orders
WHERE status = 'Shipped'
GROUP BY customerNumber) t;Code language: SQL (Structured Query Language) (sql)

Try It Out