In MySQL, the STD() function returns the population standard deviation of a given expression.

STD() is a synonym for the standard SQL function STDDEV_POP(). There’s also a STDDEV() function which also does the same thing, so we can use either of these functions to get the same result.

Syntax

The syntax for the STD() function goes like this:

STD(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 STD() function to return the population standard deviation of the DailyPrice column:

SELECT 
    Ticker,
    STD(DailyPrice)
FROM Stocks
GROUP BY Ticker
ORDER BY Ticker;

Result:

+--------+--------------------+
| Ticker | STD(DailyPrice)    |
+--------+--------------------+
| CBA    | 0.6255131226974118 |
| XRO    | 3.4401679545562276 |
+--------+--------------------+

Window Function

We can provide an OVER clause in order to make STD() operate as a window function.

Suppose we create and populate the following table:

CREATE TABLE Dogs(
  DogName VARCHAR(20),
  Activity VARCHAR(50),
  Score INT
);

INSERT INTO Dogs VALUES
  ('Bruno', 'Fetch Stick', 43),
  ('Cooper', 'Fetch Stick', 67),
  ('Max', 'Fetch Stick', 91),
  ('Bruno', 'Wag Tail', 51),
  ('Cooper', 'Wag Tail', 72),
  ('Max', 'Wag Tail', 87);

Here’s an example of a query that uses the OVER clause:

SELECT  
    DogName, 
    Activity,  
    Score, 
    STD(Score)
OVER (PARTITION BY Activity) AS std_result
FROM Dogs
ORDER BY DogName;

Result:

+---------+-------------+-------+--------------------+
| DogName | Activity    | Score | std_result         |
+---------+-------------+-------+--------------------+
| Bruno   | Fetch Stick |    43 | 19.595917942265423 |
| Bruno   | Wag Tail    |    51 |   14.7648230602334 |
| Cooper  | Fetch Stick |    67 | 19.595917942265423 |
| Cooper  | Wag Tail    |    72 |   14.7648230602334 |
| Max     | Fetch Stick |    91 | 19.595917942265423 |
| Max     | Wag Tail    |    87 |   14.7648230602334 |
+---------+-------------+-------+--------------------+

More Information

See the MySQL documentation for more information.