MySQL Standard Deviation Functions are mathematical functions that are used to calculate the variation or dispertion between values in a dataset.

There are two types of standard deviations in MySQL, they are population standard deviation and sample standard deviation.

Popular standard deviation

The “population standard deviation” is the square root of the variance of a set of data. It calculates the amount of variation or dispersion within a population. Symbolically it is represented by σ (the Greek letter sigma).

To calculate population standard deviation, we can use the following functions:

• STD(expression): It calculates and returns the population standard deviation the fields in a particular column. If the specified row(s) doesn”t exist this function returns NULL.

• STDDEV(expression): It is same as STD() function, but it also works with oracle database.

• STDDEV_POP(expression): It is equivalent to STD() function.

Following is the mathematical formula to calculate the “population standard deviation”:

// Mathematical Formula
$sigma = sqrt{frac{sum_{i=1}^n{(x-bar x)^2}}{N-1}}$

Where,

• σ = population standard deviation

• N = size of the population

• Xi = each value from the population

• meu = the population mean

Example

First, let us create a table with the name CUSTOMERS using the CREATE statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 2),
   PRIMARY KEY(ID)
);

Now, let us insert values into the CUSTOMERS table using the INSERT statement −

INSERT INTO CUSTOMERS VALUES 
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(3, ''Kaushik'', ''23'', ''Kota'', 2000),
(4, ''Chaitali'', ''26'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(6, ''Komal'', ''22'', ''Hyderabad'', 9000),
(7, ''Muffy'', ''24'', ''Indore'', 5500);

The table is created as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

The STD() Function

The following query calculates the population standard deviation of scores of all players in exhibition match −

SELECT STD(AGE) from CUSTOMERS;

Output

Following is the output −

STD(AGE)
3.063944369932459

The STDDEV() Function

The STDDEV() function is the same as STD() function, but it will also work with oracle database.

In the following query, we are calculating the population standard deviation on “Score_In_Exhibition_Match” column −

SELECT STDDEV(AGE) FROM CUSTOMERS;

Output

The output is produced as follows −

STDDEV(AGE)
3.063944369932459

The STDDEV_POP() Function

In MySQL, the STDDEV_POP() function is equivalent to the STD() function. Here, we are performing the population standard deviation on AGE column of CUSTOMERS table.

SELECT STDDEV_POP(AGE) FROM CUSTOMERS;

Output

The output is displayed as follows −

STDDEV_POP(AGE)
3.063944369932459

Sample Standard Deviation

The MySQL standard deviation is the square root of the variance, which calculates how dispersed or spread out the data is.

The STDDEV_SAMP() function is used to calculate the sample standard deviation of a set of values in a column.

Following is the formula to calculate the “Sample Standard Deviation”:

// Mathematical formula
s = sqrt(sum((x - mean)^2) / (n - 1))

Example

In the following example, let us calculate the sample standard deviation on the AGE column of the previously created CUSTOMERS table −

SELECT STDDEV_SAMP(AGE) FROM CUSTOMERS;

Output

The output is displayed as follows −

STDDEV_SAMP(AGE)
3.309438162646486