MySQL VARIANCE() Function

The MySQL VARIANCE() function computes the population variance (square of the population standard deviation) for all non-null input values ​​and returns the result. It is an alias for VAR_POP().

VARIANCE() Syntax

Here is the syntax for MySQL VARIANCE() function:

VARIANCE(expr)

We usually use the VARIANCE() function like this:

SELECT VARIANCE(expr), ...
FROM table_name
[WHERE ...];

Or use the VARIANCE() function with the GROUP BY clause:

SELECT VARIANCE(expr), group_expr1, group_expr2, ...
FROM table_name
[WHERE ...]
GROUP BY group_expr1, group_expr2, ...;

Parameters

expr

Required. A column name or expression. It accepts a numeric or binary value.

Return value

The MySQL VARIANCE() function returns the population variance (square of the population standard deviation) for all non-null input values.

Note that the VARIANCE() function only handles non-null values. That is, null values ​​are ignored by the VARIANCE() function.

If all input values ​​are null, the function will return NULL.

VARIANCE() Examples

To demonstrate usages of the MySQL BIT_AND() function, we simulate a temporary table using the following statement and UNION and SELECT:

SELECT 4 x
UNION
SELECT 5 x
UNION
SELECT 6 x;

+---+
| x |
+---+
| 4 |
| 5 |
| 6 |
+---+
3 rows in set (0.00 sec)

The following statement uses the VARIANCE() function to calculate x the population variance of all the values ​​in the column:

SELECT VARIANCE(x)
FROM (
    SELECT 4 x
    UNION
    SELECT 5 x
    UNION
    SELECT 6 x
  ) t;

+--------------------+
| VARIANCE(x)        |
+--------------------+
| 0.6666666666666666 |
+--------------------+