SQL - STDEVP() Function

The SQL STDEVP() function calculates the population standard deviation for the fields (numerical values) in a particular column. If the specified row(s) doesn’t exist, then this function returns NULL.

The population standard deviation is a measure of how much the values in a dataset deviate from the mean. Mathematically, it is the square root of the variance of the population. Symbolically it is represented by σ. It is useful because it provides a measure of how much the values in a population are spread out or clustered together around the mean. A larger value of σ indicates that the values are more widely spread out, while a smaller value indicates that they are more tightly clustered around the mean.

Syntax

Following is the syntax of SQL STDEVP() function −

 STDEVP(column_name) 

Parameters

• column_name − It is the name of the column for which we want to calculate the standard deviation population.

Example

Assume we have created a table with name CUSTOMERS as shown below −

 create table CUSTOMERS(ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID)); 

Let us insert r values into it −

 insert INTO CUSTOMERS VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00); insert INTO CUSTOMERS VALUES(2, 'Khilan', 25, 'Delhi', 1500.00); insert INTO CUSTOMERS VALUES(3, 'kaushik', 23, 'Kota', 2000.00); insert INTO CUSTOMERS VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00); insert INTO CUSTOMERS VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00); insert INTO CUSTOMERS VALUES(6, 'Komal', 22, 'MP', 4500.00); insert INTO CUSTOMERS VALUES(7, 'Muffy', 24, 'Indore', 10000.00); 

The table will be 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 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+ 

Following query calculates the population standard deviation of salary of all the customers −

 SELECT STDEVP(SALARY) as st_devp from CUSTOMERS 

Output

 +------------------+ | st_devp | +------------------+ | 3162.27766016838 | +------------------+ 

Example

The following query returns all the customers whose salary is greater than two times salary population standard deviation −

 SELECT NAME, SALARY, AGE,ADDRESS FROM CUSTOMERS WHERE SALARY > (SELECT STDEVP(SALARY * 2) FROM CUSTOMERS) 

Since, we all know from the above example that two times of salary population standard deviation is approximately equals to 6831.3004. Hence, while executing the above code we get the following output −

 +----------+----------+-----+---------+ | NAME | SALARY | AGE | ADDRESS | +----------+----------+-----+---------+ | Chaitali | 6500.00 | 25 | Mumbai | | Hardik | 8500.00 | 27 | Bhopal | | Muffy | 10000.00 | 24 | Indore | +----------+----------+-----+---------+ 

Example

Now, suppose based on the above table we want to calculate the population standard deviation of distinct (unique) age of the customers, then we can do so simply using the following query −

 SELECT STDEVP(DISTINCT AGE) as st_devp from CUSTOMERS 

Output

 +------------------+ | st_devp | +------------------+ | 3.30403793359983 | +------------------+ 

Example

In here, we are trying to get the population standard deviation of salary of all customers whose age is greater than 24 −

 SELECT STDEVP(Salary) AS st_devp FROM CUSTOMERS WHERE AGE > 24; 

Output of the above code is as follows −

 +------------------+ | st_devp | +------------------+ | 2965.95262942617 | +------------------+