MySQL VAR_SAMP Function Example 1

MySQL VAR_SAMP Function

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

The mathematical formula behind the VAR_SAMP to calculate the sample var is

--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)

The syntax behind the VAR_SAMP function in MySQL is

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

How to write VAR_SAMP function to calculate the sample Variance with an example using the below-shown data?.

MySQL VAR_SAMP Example 1

MySQL VAR_SAMP Example

The VAR_SAMP function returns the Simple Variance of total records present in a specified column. For example, the below query calculates the same of all the records present in Yearly_Income from the Customer details table.

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

VAR_SAMP Group By Example

In general, the MySQL VAR_SAMP function calculates the Sample Variance of products belonging to a particular category or color, etc.

In this situation, you can use the GROUP BY Clause in MySQL to group the products by category. And next, use the Aggregate Function to calculate the Sample Var.

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

The above SELECT Statement query group the Customers by their Profession, and calculates their Sample Variance

MySQL VAR_SAMP Function Example 2

We are taking the Software Developer profession, and showing you the MySQL VAR_SAMP output.

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

Here, we are calculating Sample Variance.
Variance = (70,000 – 74,500)² + (79,000 – 74,500)² / (2-1)
= 40,500,000