STDDEV()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
See STDDEV_
USAGE
STDDEV(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
In this example, all row values are used to compute the standard deviation (ignoring NULL values):
SELECT STDDEV(count)
FROM inventory;
+-----------------+
| STDDEV(count) |
+-----------------+
| 30.902744082686 |
+-----------------+
With GROUP BY
Using STDDEV() 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(count)
FROM inventory
GROUP BY category
ORDER BY category;
+------------+-----------------+
| category | STDDEV(count) |
+------------+-----------------+
| back order | 21.60246899477 |
| in stock | 36.348143006211 |
+------------+-----------------+
With NULL Values
When STDDEV() acts on NULL values, those values are not included when computing the standard deviation. In this example, some rows in the data set contain NULL values in the count column:
SELECT *
FROM inventory
WHERE item = 'FooBar'
ORDER BY count;
+--------+------------+-------+
| item | category | count |
+--------+------------+-------+
| FooBar | back order | NULL |
| FooBar | in stock | 10 |
+--------+------------+-------+
The following example computes a standard deviation of the count column for the FooBar item:
SELECT STDDEV(count)
FROM inventory
WHERE item = 'FooBar';
+---------------+
| STDDEV(count) |
+---------------+
| 0 |
+---------------+
With Only NULL Values
When STDDEV() is acting only on NULL values, STDDEV() returns NULL. In this example, all rows in the data set contain NULL values in the count column:
SELECT *
FROM inventory
WHERE item = 'BarBaz'
ORDER BY count;
+--------+------------+-------+
| item | category | count |
+--------+------------+-------+
| BarBaz | in stock | NULL |
| BarBaz | back order | NULL |
+--------+------------+-------+
The following example computes a standard deviation of the count column for the BarBaz item:
SELECT STDDEV(count)
FROM inventory
WHERE item = 'BarBaz';
+---------------+
| STDDEV(count) |
+---------------+
| NULL |
+---------------+