MySQL VAR_SAMP() Function

MySQL VAR_SAMP() returns the sample variance of a numeric expression. It uses the number of non-NULL observations minus one as the denominator:

sum((value - mean)²) / (number_of_non_NULL_values - 1)

Syntax

VAR_SAMP(expr) [OVER (window_definition)]

• expr: A numeric expression whose sample variance you want to calculate.
• OVER (...): Optional. Use this form to calculate the variance over a window while retaining one result row per input row.

VAR_SAMP() ignores NULL values and returns NULL when there are no matching non-NULL values. For numeric arguments, the result type is DOUBLE.

Example

Calculate the sample variance of the values 4, 5, and 6:

SELECT VAR_SAMP(x) AS sample_variance
FROM (
    SELECT 4 AS x
    UNION ALL SELECT 5
    UNION ALL SELECT 6
) AS measurements;

+-----------------+
| sample_variance |
+-----------------+
|               1 |
+-----------------+

The mean is 5, so the squared deviations sum to 2; dividing by 3 - 1 gives a sample variance of 1.

Do not confuse sample variance with population variance: VAR_POP() uses the number of values as its denominator, and VARIANCE() is a synonym for VAR_POP() in MySQL.

For complete syntax and behavior, see MySQL’s official aggregate function documentation.