MySQL - VAR_POP() Function

The MySQL VAR_POP() function calculates and returns population standard variance of a particular column. It measures how much each number in the set differs from the average (mean) of the entire set. If the specified row(s) doesn't exist this function returns NULL.

This function is only applied on columns/fields with numeric datatype as mathematical operations are applied on them.

Syntax

Following is the syntax of MySQL VAR_POP() function −

VAR_POP(expr);

Parameters

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

  • expr: An expression or column for which we want to calculate the population standard variance.

Return value

This function calculates and returns population standard variance of a particular column.

Example

First of all, let us create 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 inserts 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 );

Execute the following query to fetch all the records that are inserted in the CUSTOMERS table −

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 VAR_POP() function to calculate the population standard variance of salaries of all the customers −

SELECT VAR_POP(SALARY) FROM CUSTOMERS;

Output

This will produce the following result −

VAR_POP(SALARY)
10000000

Example

Let us consider a scenario where there is same value in the data set −

In the CUSTOMERS table, the customer with ID 2 and 5 has same age. If we calculate the variance of their age, the VAR_POP() function will return 0 as output because both has same age values.

SELECT VAR_POP(AGE) FROM CUSTOMERS
WHERE ID = 2;

The output for the query above is produced as given below −

VAR_POP(AGE)
0

Now, let us consider a scenario where there is only one value in the data set −

SELECT VAR_POP(AGE) FROM CUSTOMERS
WHERE ID = 3;

The result for the above query will also be 0 because there's only customer with age 23 −

VAR_POP(AGE)
0

Now, let us consider a scenario where there is no data in the data set −

SELECT VAR_POP(AGE) FROM CUSTOMERS
WHERE ID = 10;

The result of this query will be NULL because there are no values present for ID = 10 in the table −

VAR_POP(AGE)
NULL
Advertisements