STDDEV_SAMP()
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 sample standard deviation of the aggregated values.
USAGE
STDDEV_SAMP(number)
Argument Name | Description |
|---|---|
| The number to be aggregated |
DETAILS
STDDEV_SAMP() is an aggregate function that returns the sample 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.
The "sample" standard deviation is used when only a sample of data is available or used in computing the standard deviation.
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 sample standard deviation (ignoring NULL values):
SELECT STDDEV_SAMP(count)
FROM inventory;
+--------------------+
| STDDEV_SAMP(count) |
+--------------------+
| 33.378778544758 |
+--------------------+
With GROUP BY
Using STDDEV_SAMP() with GROUP BY creates a separate sample standard deviation for each group. In this example, GROUP BY category creates a separate standard deviation for each unique category:
SELECT category, STDDEV_SAMP(count)
FROM inventory
GROUP BY category
ORDER BY category;
+------------+--------------------+
| category | STDDEV_SAMP(count) |
+------------+--------------------+
| back order | 26.457513110646 |
| in stock | 41.971220298319 |
+------------+--------------------+
With Only NULL Values
When STDDEV_SAMP() 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 |
+--------+------------+-------+
The following example computes a standard deviation of the count column for the BarBaz item:
SELECT STDDEV_SAMP(count)
FROM inventory
WHERE item = 'BarBaz';
+--------------------+
| STDDEV_SAMP(count) |
+--------------------+
| NULL |
+--------------------+