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.