MySQL VAR_SAMP() Function

Summary: in this tutorial, you will learn how to use the MySQL VAR_SAMP() function to calculate the sample variance of values in a column of a table.

Introduction to the MySQL VAR_SAMP() function

The VAR_SAMP() function is an aggregate function that returns the sample variance of values in a column of a table.

Here’s the syntax of the VAR_SAMP() function:

SELECT VAR_SAMP(column_name)
FROM table_name;Code language: SQL (Structured Query Language) (sql)

The VAR_SAMP() returns NULL if the values are NULL or there are no matching rows.

In practice, you use the VAR_SAMP() function when you have a sample from the population, not the entire population.

Note that to calculate the population variance, you use the VAR_POP() or VARIANCE() function.

MySQL VAR_SAMP() function example

First, create a new table called apples:

CREATE TABLE apples(
   id INT AUTO_INCREMENT,
   color VARCHAR(255) NOT NULL,
   weight DECIMAL(6,2) NOT NULL,
   PRIMARY KEY(id)
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the apples table:

INSERT INTO apples (color, weight) VALUES
    ('Red', 0.6),
    ('Green', 0.4),
    ('Yellow', 0.35),
    ('Red', 0.28),
    ('Green', 0.42),
    ('Orange', 0.38),
    ('Red', 0.31),
    ('Purple', 0.45),
    ('Green', 0.37),
    ('Yellow', 0.33);Code language: SQL (Structured Query Language) (sql)

Third, query data from the apples table:

SELECT * FROM apples;Code language: SQL (Structured Query Language) (sql)

Output:

+----+--------+--------+
| id | color  | weight |
+----+--------+--------+
|  1 | Red    |   0.60 |
|  2 | Green  |   0.40 |
|  3 | Yellow |   0.35 |
|  4 | Red    |   0.28 |
|  5 | Green  |   0.42 |
|  6 | Orange |   0.38 |
|  7 | Red    |   0.31 |
|  8 | Purple |   0.45 |
|  9 | Green  |   0.37 |
| 10 | Yellow |   0.33 |
+----+--------+--------+Code language: SQL (Structured Query Language) (sql)

Finally, calculate the sample variance of the weight of the apples table:

SELECT color, VAR_SAMP(weight)
FROM apples
GROUP BY color;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+------------------------+
| color  | VAR_SAMP(weight)       |
+--------+------------------------+
| Red    |   0.031233333333333325 |
| Green  |  0.0006333333333333332 |
| Yellow | 0.00019999999999999868 |
| Orange |                   NULL |
| Purple |                   NULL |
+--------+------------------------+Code language: SQL (Structured Query Language) (sql)

Summary

Use the MySQL VAR_SAMP() function to calculate the sample variance of values in a column of a table.

Was this tutorial helpful?