STD

Syntax

STD(expr)

Description

Returns the population standard deviation of expr. This is an extension to standard SQL. The standard SQL function STDDEV_POP() can be used instead.

It is an aggregate function, and so can be used with the GROUP BY clause.

STD() can be used as a window function.

This function returns NULL if there were no matching rows.

Examples

As an aggregate function:

CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);  INSERT INTO stats VALUES   ('a',1),('a',2),('a',3),  ('b',11),('b',12),('b',20),('b',30),('b',60);  SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)   FROM stats GROUP BY category; +----------+---------------+----------------+------------+ | category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) | +----------+---------------+----------------+------------+ | a | 0.8165 | 1.0000 | 0.6667 | | b | 18.0400 | 20.1693 | 325.4400 | +----------+---------------+----------------+------------+

As a window function:

CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);  INSERT INTO student_test VALUES   ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),   ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),   ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),   ('Tatiana', 'SQL', 87);  SELECT name, test, score, STDDEV_POP(score)   OVER (PARTITION BY test) AS stddev_results FROM student_test; +---------+--------+-------+----------------+ | name | test | score | stddev_results | +---------+--------+-------+----------------+ | Chun | SQL | 75 | 16.9466 | | Chun | Tuning | 73 | 24.1247 | | Esben | SQL | 43 | 16.9466 | | Esben | Tuning | 31 | 24.1247 | | Kaolin | SQL | 56 | 16.9466 | | Kaolin | Tuning | 88 | 24.1247 | | Tatiana | SQL | 87 | 16.9466 | +---------+--------+-------+----------------+

See Also

• STDDEV_POP (equivalent, standard SQL)

• STDDEV (equivalent, Oracle-compatible non-standard SQL)

• VAR_POP (variance)

• STDDEV_SAMP (sample standard deviation)

_{This page is licensed: GPLv2, originally from fill_help_tables.sql}