MySQL VARIANCE() function

Last update on March 02 2026 12:07:11 (UTC/GMT +8 hours)

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

+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+
| invoice_no | invoice_dt | ord_no         | ord_date   | receive_dt | book_id | book_name                       | pub_lang | cate_id | receive_qty | purch_price | total_cost |
+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+
| INV0001    | 2008-07-15 | ORD/08-09/0001 | 2008-07-06 | 2008-07-19 | BK001   | Introduction to Electrodynamics | English  | CA001   |          15 |       75.00 |    1125.00 |
| INV0002    | 2008-08-25 | ORD/08-09/0002 | 2008-08-09 | 2008-08-28 | BK004   | Transfer  of Heat and Mass      | English  | CA002   |           8 |       55.00 |     440.00 |
| INV0003    | 2008-09-20 | ORD/08-09/0003 | 2008-09-15 | 2008-09-23 | BK005   | Conceptual Physics              | NULL     | CA001   |          20 |       20.00 |     400.00 |
| INV0004    | 2007-08-30 | ORD/07-08/0005 | 2007-08-22 | 2007-08-30 | BK004   | Transfer  of Heat and Mass      | English  | CA002   |          15 |       35.00 |     525.00 |
| INV0005    | 2007-07-28 | ORD/07-08/0004 | 2007-06-25 | 2007-07-30 | BK001   | Introduction to Electrodynamics | English  | CA001   |           8 |       25.00 |     200.00 |
| INV0006    | 2007-09-24 | ORD/07-08/0007 | 2007-09-20 | 2007-09-30 | BK003   | Guide to Networking             | Hindi    | CA003   |          20 |       45.00 |     900.00 |
+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+

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)

PREV : VAR_POP()
NEXT : Introduction of MySQL functions and operators