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.