MySQL - VAR_SAMP() Function

---

In general, aggregation is a consideration of a collection of objects that are bound together as a single entity.

MySQL provides a set of aggregate functions that perform operations on all the entities of the column of a table considering them as a single unit.

The mean is an average value of given set of number. A variance is defined as the average of Squared differences from mean value.

The MySQL VAR_SAMP() function calculates and returns the sample variance of all the fields in a particular column. If the specified row(s) doesn’t exist this function returns NULL.

Syntax

Following is the syntax of this function –

VAR_SAMP(expr);

Example 1

Following is an example demonstrating the usage of this function. Assume we have created a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Socre_In_Exhibiiton_match INT,
	COUNTRY VARCHAR(100),
	PRIMARY KEY (ID)
);

This table stores the first and last names, country, scores in an exhibition match of a player. Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', 95, 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', 50, 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', 25, 'Sri Lanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', 50, 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', 25, 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', 15, 'India');
insert into MyPlayers values(7, 'James', 'Anderson', 15, 'England');

Following query calculates the sample variance of scores of all the players in an exhibition match –

mysql> SELECT VAR_SAMP(Socre_In_Exhibiiton_match) from MyPlayers;
+-------------------------------------+
| VAR_SAMP(Socre_In_Exhibiiton_match) |
+-------------------------------------+
|                   820.2380952380951 |
+-------------------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function. Assume we have created another table named employee_tbl and inserted records in it as follows –

mysql> CREATE TABLE employee_tbl (
	id INT,
	name VARCHAR(255),
	Work_date INT,
	daily_typing_pages INT
);
mysql> insert into employee_tbl values(1, John, DATE('2007-01-24'), 250);
mysql> insert into employee_tbl values(2, 'Ram', DATE('2007-05-27'), 220);
mysql> insert into employee_tbl values(3, 'Jack', DATE('2007-05-06'), 170);
mysql> insert into employee_tbl values(3, 'Jack', DATE('2007-04-06'), 100);
mysql> insert into employee_tbl values(4, 'Jill', DATE('2007-04-06'), 220);
mysql> insert into employee_tbl values(5, 'Zara', DATE('2007-06-06'), 300);
mysql> insert into employee_tbl values(5, 'Zara', DATE('2007-02-06'), 350);

Now, suppose based on the above table you want to calculate the sample variance of the column dialy_typing_pages, then you can do so by using the following command −

mysql> SELECT VAR_SAMP(daily_typing_pages) FROM employee_tbl;
+------------------------------+
| VAR_SAMP(daily_typing_pages) |
+------------------------------+
|            6733.333333333333 |
+------------------------------+
1 row in set (0.00 sec)

Example 3

Let us create a table named student and inserted records into it using CREATE and INSERT statements as shown below –

mysql> CREATE TABLE student (name VARCHAR(15), marks INT, grade CHAR);
mysql> INSERT INTO student VALUES ('Raju', 80, 'A');
mysql> INSERT INTO student VALUES ('Rahman', 60, 'B');
mysql> INSERT INTO student VALUES ('Robert', 45, 'C');

Following query prints the sample variance value of the marks column of the student table –

mysql> SELECT VAR_SAMP(marks) from student;
+-------------------+
|   VAR_SAMP(marks) |
+-------------------+
| 308.3333333333333 |
+-------------------+
1 row in set (0.00 sec)

Example 4

Assume we have created and populated a table with name Sales.

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDate date,
	DeliveryTime time,
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)
INSERT INTO SALES values(1, 'Key-Board', 'Raja', DATE('2019-09-01'), TIME('11:00:00'), 7000, 'Hyderabad');
INSERT INTO SALES values(2, 'Earphones', 'Roja', DATE('2019-05-01'), TIME('11:00:00'), 2000, 'Vishakhapatnam');
INSERT INTO SALES values(3, 'Mouse', 'Puja', DATE('2019-03-01'), TIME('10:59:59'), 3000, 'Vijayawada');
INSERT INTO SALES values(4, 'Mobile', 'Vanaja', DATE('2019-03-01'), TIME('10:10:52'), 9000, 'Chennai');
INSERT INTO SALES values(5, 'Headset', 'Jalaja', DATE('2019-04-06'), TIME('11:08:59'), 6000, 'Goa');

Following query prints the sample variance value of the Price column –

ysql> SELECT VAR_SAMP(Price) FROM SALES;
+-----------------+
| VAR_SAMP(Price) |
+-----------------+
|         8300000 |
+-----------------+
1 row in set (0.00 sec)