MySQL VAR_POP Function Example 1

MySQL VAR_POP Function

MySQL VAR_POP is one of the Aggregate Functions, which calculates the Standard Variance of total records (or rows) selected by the SELECT Statement.

The VARIANCE Function is the synonym for the standard VAR_POP function. So, you can use this function also. The mathematical formulas behind the VAR_POP to calculate the standard variance in MySQL is

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

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

The basic syntax behind the VARIANCE, VAR_POP s

-- example
SELECT VARIANCE(Column_Name)
FROM Source;

-- example
SELECT VAR_POP(Column_Name)
FROM Source;

In this article, we show you, How to write these methods to calculate the standard Variance with example. For this, We are going to use the below-shown MySQL data.

Source Table 1

MySQL VAR_POP Example

The VAR_POP function returns the Standard Variance of the total number of records present in the specified column. For example, the below query will calculate the Standard Variance of total records present in Yearly_Income from the Customer details table.

SELECT VARIANCE(Yearly_Income) AS `Standard income Variance`
FROM customerdetails;

SELECT VAR_POP(Yearly_Income) AS `Standard income Variance`
FROM customerdetails;
VAR_POP Example 1

Group By Example

In general, this function calculates the Standard Variance of products belonging to a particular category or color, etc.

In these situations, we can use the GROUP BY Clause to group the products by color or category. And next, we use the Aggregate Function to calculate the Standard Variance.

USE company;
SELECT  Profession,
        VAR_POP(Yearly_Income),
        VARIANCE(Yearly_Income)
FROM customerdetails
GROUP BY Profession;

Above SELECT Statement query group the Customers by their Profession, and determine their Standard Var.

MySQL VAR_POP Function Example 2

We are taking the Software Developer profession to demonstrate VAR_POP or VARIANCE functions.

–Calculating Mean
Mean = (70,000 + 79,000) / 2
Mean = 74500

–Calculating Variance
= (70000 – 74500)² + (79000 – 74500)² / 2
= 20,250,000