VAR_POP()
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 population variance across a set of aggregated values.
USAGE
VAR_POP(number)
Argument Name | Description |
|---|---|
| The number to be aggregated |
DETAILS
VAR_POP() is an aggregate function that returns the 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.
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_POP(), 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 population variance over these item counts:
SELECT VAR_POP(count)
FROM inventory;
+----------------+
| VAR_POP(count) |
+----------------+
| 954.97959184 |
+----------------+
With GROUP BY
Using VAR_POP() 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, VAR_POP(count)
FROM inventory
GROUP BY category
ORDER BY category;
+------------+----------------+
| category | VAR_POP(count) |
+------------+----------------+
| back order | 466.66666667 |
| in stock | 1321.18750000 |
+------------+----------------+
With Only NULL Values
When VAR_POP() is acting only on NULL values, the return value is NULL:
SELECT VAR_POP(count)
FROM inventory
WHERE item = 'BarBaz';
+----------------+
| VAR_POP(count) |
+----------------+
| NULL |
+----------------+