VAR_SAMP()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns the sample variance across a set of aggregated values.
USAGE
VAR_SAMP(number)
Argument Name | Description |
|---|---|
| The number to be aggregated |
DETAILS
VAR_SAMP() is an aggregate function that returns the sample variance of a set of numeric values.
"Variance" is a measure of the amount of spread a set of values has from the mean of those values. "Sample" variance is used when only a sample of data is used in computing the variance.
Any NULL values are ignored when computing the variance value.
A NULL is returned if no non-NULL values were aggregated.
Neither DISTINCT nor ALL are allowed to be specified for VAR_SAMP(), and thus redundant duplicates are not removed from the input values.
EXAMPLES
Example Schema and Data
All of the examples are based on the inventory table:
CREATE TABLE inventory (
item VARCHAR(20),
category VARCHAR(20),
count INT
);
INSERT INTO inventory VALUES
('Foo','in stock', 42), ('Foo','back order', 50),
('Bar','in stock', 10), ('Bar','back order', 60),
('Baz','in stock', 99), ('Baz','back order', 10),
('FooBar','in stock', 10), ('FooBar','back order', NULL),
('BarBaz','in stock', NULL), ('BarBaz','back order', NULL);
The values to be computed:
SELECT item, count
FROM inventory;
+--------+-------+
| item | count |
+--------+-------+
| Foo | 42 |
| Foo | 50 |
| Bar | 10 |
| Bar | 60 |
| Baz | 99 |
| Baz | 10 |
| FooBar | 10 |
| FooBar | NULL |
| BarBaz | NULL |
| BarBaz | NULL |
+--------+-------+
Default Behavior
The result of computing sample variance over these item counts:
SELECT VAR_SAMP(count)
FROM inventory;
+-----------------+
| VAR_SAMP(count) |
+-----------------+
| 1114.14285714 |
+-----------------+
With GROUP BY
Using VAR_SAMP() with GROUP BY creates a separate sample variance for each group. In this example, GROUP BY category creates a separate sample variance for each unique category:
SELECT category, VAR_SAMP(count)
FROM inventory
GROUP BY category
ORDER BY category;
+------------+-----------------+
| category | VAR_SAMP(count) |
+------------+-----------------+
| back order | 700.00000000 |
| in stock | 1761.58333333 |
+------------+-----------------+
With Only NULL Values
When VAR_SAMP() is acting only on NULL values, the return value is NULL:
SELECT VAR_SAMP(count)
FROM inventory
WHERE item = 'BarBaz';
+-----------------+
| VAR_SAMP(count) |
+-----------------+
| NULL |
+-----------------+