MySQL in a Nutshell, 2nd Edition

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.