In MySQL, the STDDEV_SAMP()
function returns the sample standard deviation of a given expression. This is the square root of VAR_SAMP()
.
STDDEV_SAMP()
is an aggregate function, and so it can be used with the GROUP BY
clause.
If there are no matching rows, or if the expression is NULL
, STDDEV_SAMP()
returns NULL
.
Syntax
The syntax for the STDDEV_SAMP()
function goes like this:
STDDEV_SAMP(expr) [over_clause]
Where expr
is the target column or expression that the function operates on.
The [over_clause]
part means that we also have the option of providing an over clause to make the function execute as a window function.
Example
Suppose we create the following table and insert data:
CREATE TABLE Stocks(
Ticker VARCHAR(20),
DailyPrice DECIMAL(10,2)
);
INSERT INTO Stocks VALUES
('CBA', 107.52),
('CBA', 106.99),
('CBA', 108.50),
('XRO', 89.11),
('XRO', 93.89),
('XRO', 97.51);
The following query is an example of using the STDDEV_SAMP()
function to return the sample standard deviation of the DailyPrice
column, grouped by the Ticker
column:
SELECT
Ticker,
STDDEV_SAMP(DailyPrice)
FROM Stocks
GROUP BY Ticker
ORDER BY Ticker;
Result:
+--------+-------------------------+ | Ticker | STDDEV_SAMP(DailyPrice) | +--------+-------------------------+ | CBA | 0.7660939890117929 | | XRO | 4.2133280590684326 | +--------+-------------------------+
Window Function
We can provide an OVER
clause in order to make STDDEV_SAMP()
operate as a window function.
Suppose we have a table called t1
that looks like this:
SELECT * FROM t1
ORDER BY c1;
Result:
+------+------+ | c1 | c2 | +------+------+ | 1 | 3 | | 1 | 5 | | 2 | 6 | | 3 | 9 | | 3 | 9 | | 4 | 8 | | 5 | 7 | | 5 | 6 | | 6 | 4 | | 7 | 2 | | 7 | 5 | | 8 | 2 | | 9 | 10 | | 9 | 5 | | 10 | 1 | +------+------+
Here’s an example of a query that uses STDDEV_SAMP()
with the OVER
clause against that table:
SELECT
c1,
STDDEV_SAMP(c1) OVER (PARTITION BY c2) AS stdev_samp_c1
FROM t1
ORDER BY c1, stdev_samp_c1;
Result:
+------+--------------------+ | c1 | stdev_samp_c1 | +------+--------------------+ | 1 | NULL | | 1 | 4.163331998932265 | | 2 | 2.1213203435596424 | | 3 | 0 | | 3 | 0 | | 4 | NULL | | 5 | NULL | | 5 | 2.1213203435596424 | | 6 | NULL | | 7 | 0.7071067811865476 | | 7 | 4.163331998932265 | | 8 | 0.7071067811865476 | | 9 | NULL | | 9 | 4.163331998932265 | | 10 | NULL | +------+--------------------+
More Information
See the MySQL documentation for more information.