How VARIANCE() Works in MySQL

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

If there are no matching rows, or if the expression is NULL, the function returns NULL.

The VARIANCE() function is a synonym for the standard SQL VAR_POP() function. In other words, they both do the same thing, but VARIANCE() is not standard SQL. Therefore, if you need to use standard SQL, use VAR_POP() instead.

Syntax

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

VARIANCE(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 VARIANCE() function to return the population standard variance of the DailyPrice column:

SELECT VARIANCE(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,
    VARIANCE(DailyPrice)
FROM Stocks
GROUP BY Ticker
ORDER BY Ticker;

Result:

+--------+----------------------+
| Ticker | VARIANCE(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 VARIANCE() 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 VARIANCE() with the OVER clause against that table:

SELECT  
    DogName, 
    Activity,  
    Score, 
    VARIANCE(Score)
OVER (PARTITION BY Activity) AS variance_result
FROM Dogs
ORDER BY DogName;

Result:

+---------+-------------+-------+-----------------+
| DogName | Activity    | Score | variance_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.