Name
VAR_SAMP()
Synopsis
VAR_SAMP(expression
)
This function returns the variance of a given column, based
on the rows selected as a sample of a given 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. To determine the variance based on the entire
population rather than a sample, use VAR_POP().
Both of these functions were added in version 5.0.3 of MySQL for
compliance with SQL standards. Here is an example of both:
SELECT CONCAT(name_first, SPACE(1), name_last) AS rep_name, AVG(sale_amount) AS avg_sale, STDDEV_POP(sale_amount) AS population_std_dev, STDDEV_SAMP(sale_amount) AS sample_std_dev, VAR_POP(sale_amount) AS population_variance, VAR_SAMP(sale_amount) AS sample_variance FROM sales JOIN sales_reps USING(sales_rep_id) GROUP BY sales_rep_id;
This SQL statement uses several 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 STDDEV_SAMP() to determine the standard deviation from the average based on a sample of the data. It also includes VAR_POP() to show the variances based on the population, and VAR_SAMP() to return the variance based on the sample data.
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.