STDDEV
On this page
Returns the standard deviation of all the values in a specified expression.
May be used as a window function and with the frame clause.
Syntax
Aggregate Function
STDDEV( [ DISTINCT ] <expression>
Window Function & Window Frame Clause
STDDEV( [DISTINCT ] (col | expr ) OVER ( [ PARTITION BY (col | expr )] [ ORDER BY (col | expr ) [ <window_frame> ] ] )
Arguments
-
expression: numeric expression
Return Type
Returns the standard deviation of the expression or NULL if there are no matching rows.
Examples
The following table and values were used for the examples:
CREATE TABLE player_scores(player_name VARCHAR(50), player_id VARCHAR(10),1st_qtr_score DECIMAL(5,2), 2nd_qtr_score DECIMAL(5,2),3rd_qtr_score DECIMAL(5,2), 4th_qtr_score DECIMAL(5,2),yearly_total AS 1st_qtr_score + 2nd_qtr_score + 3rd_qtr_score + 4th_qtr_score PERSISTED DECIMAL(5,2));INSERT INTO player_scores VALUES('Steve', '119','22.50','72.00', '63.00', '45.00'),('Jack', '432', '90.10', '78.00','88.20', '92.20'),('Jim', '779','75.00', '68.90','55.70', '72.00'),('Eve', '189','91.50', '88.10', '95.00', '94.50'),('Shelia','338', '75.60', '72.00', '81.10', '78.40'),('June', '521', '81.00', '87.30','76.80','82.20'),('Martin', '674','98.80', '95.10', '88.00', '96.40');
Aggregate Function
SELECT STDDEV(yearly_total) FROM player_scores;
+----------------------+
| STDDEV(yearly_total) |
+----------------------+
| 57.083158 |
+----------------------+Window Function
SELECT player_name, yearly_total,ROUND(stddev(yearly_total) OVER (ORDER BY player_name, yearly_total)) AS stddevFROM player_scoresORDER BY player_name;
+-------------+--------------+--------+
| player_name | yearly_total | stddev |
+-------------+--------------+--------+
| Eve | 369.10 | 0 |
| Jack | 348.50 | 10 |
| Jim | 271.60 | 42 |
| June | 327.30 | 36 |
| Martin | 378.30 | 38 |
| Shelia | 307.10 | 37 |
| Steve | 202.50 | 57 |
+-------------+--------------+--------+Window Frame Clause
SELECT player_name, yearly_total,ROUND(stddev(yearly_total) OVER (ORDER BY player_name, yearly_total ROWS UNBOUNDED PRECEDING)) AS stddevFROM player_scoresORDER BY player_name;
+-------------+--------------+--------+
| player_name | yearly_total | stddev |
+-------------+--------------+--------+
| Eve | 369.10 | 0 |
| Jack | 348.50 | 10 |
| Jim | 271.60 | 42 |
| June | 327.30 | 36 |
| Martin | 378.30 | 38 |
| Shelia | 307.10 | 37 |
| Steve | 202.50 | 57 |
+-------------+--------------+--------+Last modified: September 6, 2024