MySQL VAR_SAMP Aggregate Function is useful for calculating 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 is
-- 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
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;
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.
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
We are taking the Software Developer profession and showing you the 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)