MySQL - STDDEV_POP() Function



Standard deviation is the square root of the average of squared deviations of the items from their mean. Symbolically it is represented by σσ. It is of two types: population standard deviation, sample standard deviation.

MySQL STDDEV_POP() function calculates and returns the population standard deviation of the fields in a particular column. If the specified row(s) doesn't exist this function returns NULL. This result of this function will be the square root of VAR_POP().

Syntax

Following is the syntax of MySQL STDDEV() function −

STDDEV_POP(expr);

Parameters

This method accepts a parameter. The same is described below −

  • expr: An expression or a particular column for which we want to calculate the standard deviation.

Return value

This function calculates and returns the population standard deviation for the rows in a particular column.

Example

In the following query, we are creating a table named CUSTOMERS using the CREATE TABLE statement as follows −

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

The below query adds 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(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 );

To verify whether the records are inserted, execute the following query −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

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

Here, we are using the MySQL STDDEV_POP() function to calculate the population standard deviation of salaries of all the customers −

SELECT STDDEV_POP(SALARY) From CUSTOMERS;

Output

This will produce the following result −

STDDEV_POP(SALARY)
3162.2776601683795

The result of the query will be NULL because there are no records in the CUSTOMERS table where the ID is equal to 15 −

SELECT STDDEV(SALARY) From CUSTOMERS
WHERE ID = 15;

Output

This will produce the following result −

STDDEV(SALARY)
NULL
Advertisements