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.