MySQL VARIANCE() function

VARIANCE() function

MySQL VARIANCE() function returns the population standard variance of an expression. It considers the entire dataset rather than just a sample. Variance is a statistical measure that indicates the spread or dispersion of a dataset.

This function is useful in -

• It's essential for understanding the distribution of data.
• High variance indicates greater dispersion, while low variance suggests data points are closer to the mean.
• Variance can identify outliers or extreme values in dataset. Unusually high variances might signal data quality issues or anomalies.
• In finance and investment, variance is used to assess the risk associated with an investment.
• Variance can be used to analyze the consistency of performance metrics. It might indicate how consistent the product quality is.
• Variance analysis is crucial in scientific experiments and research. It helps assess the reliability of results and the consistency of measurements.
• In industries like manufacturing, calculating variance can be part of quality control processes.
• Variance can be used to compare different datasets or groups.
• Variance analysis can play a role in forecasting future outcomes by considering historical variability.

Syntax:

VARIANCE(expr);

Where expr is an expression

MySQL Version: 8.0

Example: MySQL VARIANCE() function

The following MySQL statement returns the population standard variance of 'total_cost' from purchase table.

Sample table: purchase

Code:

-- This query calculates the variance of the 'total_cost' column in the 'purchase' table.
SELECT VARIANCE(total_cost)              
-- This statement selects the variance of the 'total_cost' column.
FROM purchase;
-- This part of the query specifies the table from which data is being retrieved, which is 'purchase'.

Explanation:

• The purpose of this SQL query is to compute the variance of the 'total_cost' values in the 'purchase' table.


• SELECT VARIANCE(total_cost): This part of the query selects the variance of the 'total_cost' column. Variance measures the average degree to which each number is different from the mean, and is calculated as the average of the squared differences from the mean.


• FROM purchase: This part specifies the table from which the data is being selected, which is the 'purchase' table.

Output:

mysql> SELECT  VARIANCE(total_cost)              
    -> FROM purchase;
+----------------------+
| VARIANCE(total_cost) |
+----------------------+
|         99472.222222 | 
+----------------------+
1 row in set (0.00 sec)

Previous: VAR_POP()
Next: Introduction of MySQL functions and operators