In MySQL, the VAR_POP() function returns the population standard variance of an expression.

If there are no matching rows, or if the expression is NULL, VAR_POP() it returns NULL.

VAR_POP() is an aggregate function, and so it can be used with the GROUP BY clause.

The VAR_POP() function is standard SQL. There’s also a VARIANCE() function that does the same thing, but is not standard SQL.

Syntax

The syntax for the VAR_POP() function goes like this:

VAR_POP(expr) [over_clause]

Where expr is the target column or expression that the function operates on.

The [over_clause] part means that we also have the option of providing an over clause to make the function execute as a window function.

Example

Suppose we create the following table and insert data:

CREATE TABLE Stocks(
  Ticker VARCHAR(20),
  DailyPrice DECIMAL(10,2)
);

INSERT INTO Stocks VALUES
  ('CBA', 107.52),
  ('CBA', 106.99),
  ('CBA', 108.50),
  ('XRO', 89.11),
  ('XRO', 93.89),
  ('XRO', 97.51);

The following query is an example of using the VAR_POP() function to return the population standard variance of the DailyPrice column:

SELECT VAR_POP(DailyPrice)
FROM Stocks;

Result:

56.286622222222235

That returned the population standard variance of both stocks/tickers.

The following query is similar, but this time we group the results by the Ticker column:

SELECT 
    Ticker,
    VAR_POP(DailyPrice)
FROM Stocks
GROUP BY Ticker
ORDER BY Ticker;

Result:

+--------+---------------------+
| Ticker | VAR_POP(DailyPrice) |
+--------+---------------------+
| CBA    |  0.3912666666666674 |
| XRO    |   11.83475555555558 |
+--------+---------------------+

This enables us to see the population standard variance of each stock/ticker.

Window Function

We can provide an OVER clause in order to make VAR_POP() operate as a window function.

Suppose we create a table called Dogs and insert data:

CREATE TABLE Dogs(
  DogName VARCHAR(20),
  Activity VARCHAR(50),
  Score INT
);

INSERT INTO Dogs VALUES
  ('Bruno', 'Fetch Stick', 43),
  ('Cooper', 'Fetch Stick', 67),
  ('Max', 'Fetch Stick', 91),
  ('Bruno', 'Wag Tail', 51),
  ('Cooper', 'Wag Tail', 72),
  ('Max', 'Wag Tail', 87);

Here’s an example of a query that uses VAR_POP() with the OVER clause against that table:

SELECT  
    DogName, 
    Activity,  
    Score, 
    VAR_POP(Score)
OVER (PARTITION BY Activity) AS var_pop_result
FROM Dogs
ORDER BY DogName;

Result:

+---------+-------------+-------+----------------+
| DogName | Activity    | Score | var_pop_result |
+---------+-------------+-------+----------------+
| Bruno   | Fetch Stick |    43 |            384 |
| Bruno   | Wag Tail    |    51 |            218 |
| Cooper  | Fetch Stick |    67 |            384 |
| Cooper  | Wag Tail    |    72 |            218 |
| Max     | Fetch Stick |    91 |            384 |
| Max     | Wag Tail    |    87 |            218 |
+---------+-------------+-------+----------------+

More Information

See the MySQL documentation for more information.