VARIANCE()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
See VAR_
USAGE
VARIANCE(number)
Argument Name | Description |
|---|---|
| The number to be aggregated |
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 population variance over these item counts:
SELECT VARIANCE(count)
FROM inventory;
+-----------------+
| VARIANCE(count) |
+-----------------+
| 954.97959184 |
+-----------------+
With GROUP BY
Using VARIANCE() with GROUP BY creates a separate population variance for each group. In this example, GROUP BY category creates a separate population variance for each unique category:
SELECT category, VARIANCE(count)
FROM inventory
GROUP BY category
ORDER BY category;
+------------+-----------------+
| category | VARIANCE(count) |
+------------+-----------------+
| back order | 466.66666667 |
| in stock | 1321.18750000 |
+------------+-----------------+
With Only NULL Values
When VARIANCE() is acting only on NULL values, the return value is NULL:
SELECT VARIANCE(count)
FROM inventory
WHERE item = 'BarBaz';
+-----------------+
| VARIANCE(count) |
+-----------------+
| NULL |
+-----------------+