In MySQL, the VAR_SAMP() function returns the sample variance of an expression. The denominator is the number of rows minus one.

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

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

The VAR_SAMP() function can also be used as a window function.

Syntax

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

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

SELECT VAR_SAMP(DailyPrice)
FROM Stocks;

Result:

67.54394666666668

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

Result:

+--------+----------------------+
| Ticker | VAR_SAMP(DailyPrice) |
+--------+----------------------+
| CBA    |   0.5869000000000011 |
| XRO    |    17.75213333333337 |
+--------+----------------------+

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

Window Function

We can provide an OVER clause in order to make VAR_SAMP() 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_SAMP() with the OVER clause against that table:

SELECT  
    DogName, 
    Activity,  
    Score, 
    VAR_SAMP(Score)
OVER (PARTITION BY Activity) AS var_samp_result
FROM Dogs
ORDER BY DogName;

Result:

+---------+-------------+-------+-----------------+
| DogName | Activity    | Score | var_samp_result |
+---------+-------------+-------+-----------------+
| Bruno   | Fetch Stick |    43 |             576 |
| Bruno   | Wag Tail    |    51 |             327 |
| Cooper  | Fetch Stick |    67 |             576 |
| Cooper  | Wag Tail    |    72 |             327 |
| Max     | Fetch Stick |    91 |             576 |
| Max     | Wag Tail    |    87 |             327 |
+---------+-------------+-------+-----------------+

More Information

See the MySQL documentation for more information.