With this MySQL function the variance is determined by taking the difference between each given value and the average of all values given.
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).
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 the 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.
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 one'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.