MySQL VAR_SAMP Function

MySQL VAR_SAMP is one of the Aggregate Function, which is used to calculate the Sample Variance of total records (or rows) selected by the SELECT Statement.

MySQL VAR_SAMP Formula

The mathematical formula behind the VAR_SAMP to calculate the sample variance in MySQL is as shown below:

--Calculating Mean or Average
Mean = Sum of each individual / Total number of items

--Calculating the Sample Variance
Sample Variance = ( (OriginalValue – Mean)² + (OriginalValue – Mean)² +.... ) / (Total number of items - 1)

MySQL VAR_SAMP Syntax

The basic syntax behind the VAR_SAMP in MySQL is as shown below:

-- VAR_SAMP in MySQL Syntax
SELECT VAR_SAMP(Column_Name)
FROM Source;

In this article we will show you, How to write VAR_SAMP function in MySQL to calculate the sample Variance with example. For this, We are going to use the below shown data

MySQL VAR_SAMP Example

In MySQL, VAR_SAMP function will simply returns the Simple Variance of total records present in a specified column. For example, below query will calculate the Sample Variance of all the records present in Yearly_Income from Customer details table.

-- MySQL VAR_SAMP example
SELECT VAR_SAMP(Yearly_Income) AS `Simple income Variance`
FROM customerdetails;

OUTPUT

MySQL VAR_SAMP – Group By Example

In general, VAR_SAMP function is used to calculate the Sample Variance of a products belongs to particular category or color etc. In this situation you can use GROUP BY Clause to group the products by category. And next, use VAR_SAMP Function to calculate the Sample Variance

-- MySQL VAR_SAMP Function Example
USE company;
SELECT  Profession,
        VAR_SAMP(Yearly_Income)
FROM customerdetails
GROUP BY Profession;

Above SQL Query will group the Customers by their Profession, and calculates their Sample Variance

OUTPUT

ANALYSIS

We are taking Software Developer profession, and show you the output.

–Calculating Mean
Mean = (70000 + 79,000) / 2
Mean = 74,500

–Calculating Sample Variance
Variance = (70,000 – 74,500)² + (79,000 – 74,500)² / (2-1)
Variance = 40,500,000

Thank You for Visiting Our Blog