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.