Tutorial Gateway

MySQL VAR_POP Function

by

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

TIP : In MySQL, VARIANCE Functions 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

MySQL VAR_POP Syntax

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 will 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 1

MySQL VAR_POP Example

In MySQL, VARIANCE, VAR_POP functions will simply returns the Standard Variance of 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;

OUTPUT

MySQL VAR_POP Function Example 1

MySQL VAR_POP – Group By Example

In general, VAR_POP, or VARIANCE functions are used to calculate the Standard Variance of a products belongs to 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 SQL Query will group the Customers by their Profession, and calculates their Standard Variance

OUTPUT

MySQL VAR_POP Function Example 2

ANALYSIS

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

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

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

Thank You for Visiting Our Blog