STDDEV_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 standard deviation of the aggregated values.
USAGE
STDDEV_POP(number)
Argument Name | Description |
|---|---|
| The number to be aggregated |
DETAILS
STDDEV_POP() is an aggregate function that returns the standard deviation of a set of numeric values.
A standard deviation is a measure of the dispersion of each value's deviation from the mean of the values.
Any NULL values are completely ignored.
A NULL is returned if no numeric values are 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
In this example, all the row values are used to compute the standard deviation (ignoring NULL values):
SELECT STDDEV_POP(count)
FROM inventory;
+-------------------+
| STDDEV_POP(count) |
+-------------------+
| 30.902744082686 |
+-------------------+
With GROUP BY
Using STDDEV_POP() with GROUP BY creates a separate population standard deviation for each group. In this example, GROUP BY category creates a separate standard deviation for each unique category:
SELECT category, STDDEV_POP(count)
FROM inventory
GROUP BY category
ORDER BY category;
+------------+-------------------+
| category | STDDEV_POP(count) |
+------------+-------------------+
| back order | 21.60246899477 |
| in stock | 36.348143006211 |
+------------+-------------------+
With Only NULL Values
When STDDEV_POP() is given no numeric values, NULL is returned:
SELECT *
FROM inventory
WHERE item = 'BarBaz'
ORDER BY count;
+--------+------------+-------+
| item | category | count |
+--------+------------+-------+
| BarBaz | in stock | NULL |
| BarBaz | back order | NULL |
+--------+------------+-------+