MySQL - Standard Deviation

Table of content

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.

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
Advertisements