VAR_SAMP( )

This MySQL function returns the variance of a given column, based on the rows selected as a sample of a given population.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

VAR_SAMP(expression)

Explanation

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.

Examples

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 one'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.