MySQL VAR_POP is one of the Aggregate Function, which calculates the Standard Variance of total records (or rows) selected by the SELECT Statement.
TIP : The VARIANCE Function is the synonyms for the standard VAR_POP function. So, you can use this function also.
MySQL VAR_POP Formula
The mathematical formulas behind the VAR_POP to calculate the standard variance in MySQL is as shown below:
--Calculating the Mean or Average Mean = Sum of each individual / Total number of items --Calculating the Variance Variance = ( (OriginalValue – Mean)² + (OriginalValue – Mean)² +.... ) / Total number of items
The basic syntax behind the VARIANCE, VAR_POP in MySQL is as shown below:
-- MySQL VARIANCE example SELECT VARIANCE(Column_Name) FROM Source; -- MySQL VAR_POP example SELECT VAR_POP(Column_Name) FROM Source;
In this article, we show you, How to write VARIANCE, VAR_POP functions in MySQL to calculate the standard Variance with example. For this, We are going to use the below-shown data.
MySQL VAR_POP Example
The VARIANCE, VAR_POP functions returns the Standard Variance of the total number of records present in the specified column. For example, below query will calculate the Standard Variance of total records present in Yearly_Income from Customer details table.
-- MySQL VARIANCE example SELECT VARIANCE(Yearly_Income) AS `Standard income Variance` FROM customerdetails; -- MySQL VAR_POP example SELECT VAR_POP(Yearly_Income) AS `Standard income Variance` FROM customerdetails;
MySQL VAR_POP Group By Example
In general, VAR_POP or VARIANCE functions calculate the Standard Variance of products belongs to a particular category or color, etc.
In these situations, we can use GROUP BY Clause to group the products by color or category. And next, we use VAR_POP Function to calculate the Standard Variance.
-- MySQL VARIANCE, and VAR_POP Function Examples USE company; SELECT Profession, VAR_POP(Yearly_Income), VARIANCE(Yearly_Income) FROM customerdetails GROUP BY Profession;
Above VAR_POP and Variance query group the Customers by their Profession, and determine their Standard Variance
We are taking the Software Developer profession, and show you the output.
Mean = (70,000 + 79,000) / 2
Mean = 74500
Variance = (70000 – 74500)² + (79000 – 74500)² / 2
Variance = 20,250,000