©SQL :: Manager
HomeProgram InformationTechnical detailsFree downloadDonate for a cup of coffee
myway SQL Manager for MySQL and MariaDB, Download on Uptodown
SQL functionMySQLMariaDB

The STDDEV SQL Function in MySQL and MariaDB - Calculation of standard deviation

STDDEVSyntax:STDDEV(Expression or column)
Return value:FLOAT
Synonyms:STDDEV_POP, STD
Function type:Aggregate function, Window function
Function Description

The STDDEV() function calculates the population standard deviation of "Expression or column".

When evaluating string arguments, the function returns 0.

Using a DISTINCT clause will result in an error.

For ENUM fields, the standard deviation of the positions of the values ​​in their lists is calculated.

For SET fields, the standard deviation of the binary positions of the values ​​in their lists is calculated = POW(2, Position-1).

As an aggregate function, the STDDEV() function can be used with a GROUP BY clause.

STDDEV() can be used as a window function.

The STDDEV() function returns NULL if no result is found.

SQL Examples for the STDDEV function

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

DROP TABLE IF EXISTS `test_table`;
    
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `type` enum('First','Second','Third','Last'DEFAULT NULL,
  `set` set('First','Second','Third','Last'DEFAULT NULL,
  `num` int(11) NOT NULL DEFAULT 0,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

INSERT INTO `test_table` (`id`,`name`,`type`,`set`,`num`,`price`VALUES 
(1,'Art 1','First','Last',1,10.50),
(2,'Art 2','Second','First,Last',3,12.50),
(3,'Art 3','Second','Second,Last',2,15.50),
(4,'Art 4','Third','First,Second',5,25.50),
(5,'Art 5','Last','First,Last',2,5.50),
(6,'Art 6','First','First',12,1.50),
(7,'Art 7','Third','First,Third',20,1.75),
(8,'Art 8','Last','Second,Third',100,11.90);

select * from `test_table`;

select stddev(name) from test_table;

select stddev(num),stddev(price),stddev(`type`),stddev(`set`from test_table;
select stddev(num),stddev(price) from test_table where id>8;

/* as Window function */

SELECT name, `type`, price, stddev(price) 
  OVER (PARTITION BY `type`AS `stddev` FROM test_table;

id
name
type
set
num
price
int(11)varchar(20) LATIN1enum LATIN1set LATIN1int(11)decimal(10,2)
1Art 1FirstLast110.50
2Art 2SecondFirst,Last312.50
3Art 3SecondSecond,Last215.50
4Art 4ThirdFirst,Second525.50
5Art 5LastFirst,Last25.50
6Art 6FirstFirst121.50
7Art 7ThirdFirst,Third201.75
8Art 8LastSecond,Third10011.90
stddev(name)
double(23)
0
stddev(num)
stddev(price)
stddev(`type`)
stddev(`set`)
double(26)double(26)double(17)double(17)
31.54147.4032911.11802.9974
stddev(num)
stddev(price)
double(26)double(26)
NULLNULL
name
type
price
stddev
test_table
varchar(20) LATIN1
test_table
enum LATIN1
test_table
decimal(10,2)

double(26)
Art 6First1.504.500000
Art 1First10.504.500000
Art 2Second12.501.500000
Art 3Second15.501.500000
Art 7Third1.7511.875000
Art 4Third25.5011.875000
Art 8Last11.903.200000
Art 5Last5.503.200000
The examples were created with the MyWAY SQL manager: Download

How to use the STDDEV() function in MySQL and MariaDB databases

In MySQL and MariaDB, the STDDEV() function can be used to get the standard deviation of a set of values calculated from a table column or an expression. The function calculates the sample standard deviation, which is an estimate of the standard deviation based on the population. The calculation requires a sufficient number of data points to obtain meaningful results. If there are too few data points, the calculated standard deviation may not accurately reflect the entire population.

The standard deviation is usefull in statistical analysis to measure the amount of variation or spread in a data set, for example to analyze a data distribution, the standard deviation provides information about the spread of data points around the mean. It tracks how much the data values ​​deviate from the average to see if data is tightly clustered or widely scattered.

Further, the standard deviation can be used to compare the variability or spread between different data sets. This allows the consistency or variability of data from different groups to be assessed and compared. In financial analysis, the standard deviation is used to measure the risk or volatility of investment returns, for example to compare the performance of different investments. The standard deviation can also be used as a tool for data validation and outlier detection. The calculation can identify data points that deviate significantly from the mean.

STDDEV() has the same function as the standard SQL function STDDEV_POP().

Further MySQL und MariaDB SQL Aggregate functions

ANY_VALUEANY_VALUE(Column)
More about ANY_VALUE Function

AVGAVG(Expression or column)
More about AVG Function

BIT_ANDBIT_AND(Expression or column)
More about BIT_AND Function

BIT_ORBIT_OR(Expression or column)
More about BIT_OR Function

BIT_XORBIT_XOR(Expression or column)
More about BIT_XOR Function

COUNTCOUNT(Expression or column)
More about COUNT Function

GROUP_CONCATGROUP_CONCAT([DISTINCT] Expression [, Expression ...] [ORDER BY ... [ASC | DESC]] [SEPARATOR ...] [LIMIT ...])
More about GROUP_CONCAT Function

JSON_ARRAYAGGJSON_ARRAYAGG(Expression or column)

JSON_OBJECTAGGJSON_OBJECTAGG(Expression or column)

MAXMAX(Expression or column)
More about MAX Function

MINMIN(Expression or column)
More about MIN Function

SUMSUM(Expression or column)
More about SUM Function

STDSTD(Expression or column)
More about STD Function

STDDEV_POPSTDDEV_POP(Expression or column)
More about STDDEV_POP Function

STDDEV_SAMPSTDDEV_SAMP(Expression or column)
More about STDDEV_SAMP Function

VAR_POPVAR_POP(Expression or column)
More about VAR_POP Function

VAR_SAMPVAR_SAMP(Expression or column)
More about VAR_SAMP Function

VARIANCEVARIANCE(Expression or column)
More about VARIANCE Function

Numeric functionsString functionsRegular ExpressionsDate and Time functions
Comparison functionsEncryption & CompressionConversion functionsNULL functions
Aggregate functionsWindow functionsJSON functionsGeometric functions
Sequence functionsInformation functionsDynamic ColumnsMiscellaneous functions
More information about the STDDEV SQL function: mysql.com and mariadb.com
Updated: 2023-09-17ImprintContactTerms & conditionsPrivacy & CookiesUpdatesSitemapFacebookLinkedinTwitterStatistics©2020-2023 Heino Cunze-Fischer