Name
VARIANCE()
Synopsis
VARIANCE(expression
)
The variance is determined by taking the difference between each given
value and the average of all values given. Each of those differences
is then squared, and the results are totaled. The average of that
total is then determined to get the variance. This function returns
the variance of a given column, based on the rows selected as a
population. It’s normally used in conjunction with a GROUP BY
clause specifying a unique
column, so that values are compared for each unique item separately.
This function is available as of version 4.1 of MySQL. Here is an
example:
SELECT CONCAT(name_first, SPACE(1), name_last) AS rep_name, AVG(sale_amount) AS avg_sale, STDDEV_POP(sale_amount) AS standard_deviation, VARIANCE(sale_amount) AS variance FROM sales JOIN sales_reps USING(sales_rep_id) GROUP BY sales_rep_id;
This SQL statement uses a few aggregate functions: AVG() to determine the average sale for each sales
rep; STDDEV_POP() to determine how much each sale
made by each sales rep tends to vary from each rep’s average sale; and
VARIANCE()
to show the variances based on the
population. To comply with SQL standards, VAR_POP() could have been used instead of
VARIANCE()
.
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.