MySQL - VARIANCE() Function

The MySQL VARIANCE() function calculates and returns the population standard variance of all the values in a particular column i.e. it measures how much the values differ from the average.

The variance will be 0, if all the values in the data set are the same or if there is only one value. If there are no values at all in the dataset, the function returns NULL.

The VARIANCE() function has similar semantics of VAR_POP() function.

Syntax

Following is the syntax of MySQL VARIANCE() function −

VARIANCE(expr);

Parameters

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

  • expr: The expression or column for which we want to calculate the variance.

Return value

This function calculates and returns the population standard variance of all the values in 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 following 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 );

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

SELECT VARIANCE(SALARY) FROM CUSTOMERS;

Output

This will produce the following result −

VARIANCE(SALARY)
10000000

Example

The below query will return 0 as output because the age of id = 2 and id = 5 is same in the CUSTOMERS table −

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

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

VARIANCE(Sale_amount)
0

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

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

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

VARIANCE(Sale_amount)
0

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

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

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

VARIANCE(Sale_amount)
NULL
Advertisements