STD()
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
STD(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);
Default Behavior
In this example, all values are used to compute the standard deviation.
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 |
+--------+-------+
The result of computing standard deviation over these item counts:
SELECT STD(count)
FROM inventory;
+-----------------+
| STD(count) |
+-----------------+
| 30.902744082686 |
+-----------------+
With GROUP BY
Using STD() 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, STD(count)
FROM inventory
GROUP BY category
ORDER BY category;
+------------+-----------------+
| category | STD(count) |
+------------+-----------------+
| back order | 21.60246899477 |
| in stock | 36.348143006211 |
+------------+-----------------+
With NULL Values
When STD() 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 STD(count)
FROM inventory
WHERE item = 'FooBar';
+------------+
| STD(count) |
+------------+
| 0 |
+------------+
With Only NULL Values
When STD() is acting only on NULL values, STD() 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 STD(count)
FROM inventory
WHERE item = 'BarBaz';
+------------+
| STD(count) |
+------------+
| NULL |
+------------+