MySQL - VAR_SAMP() Function

---

---

The MySQL VAR_SAMP() function in MySQL calculates the sample variance of a set of values in a column. It measures how much the values in the sample differ from the sample mean. 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_SAMP() function −

VAR_SAMP(expr);

Parameters

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

• expr: An expression or column containing numeric values for which we want to calculate the sample variance.

Return value

This function calculates and returns sample variance of a set of values in a particular column.

Example

In the following query, we are creating a table named CUSTOMERS using the CREATE 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 );

To display the records that are inserted in the CUSTOMERS table, 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

Now, we are using the MySQL VAR_SAMP() function to calculate the sample variance of salaries of all the customers −

SELECT VAR_SAMP(SALARY) From CUSTOMERS;

Output

This will produce the following result −

VAR_SAMP(SALARY)
11666666.666666666

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

SELECT VAR_SAMP(SALARY) From CUSTOMERS
WHERE ID = 10;

Output

This will produce the following result −

VAR_SAMP(SALARY)
NULL