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

The VAR_SAMP SQL Function in MySQL and MariaDB - Sample variance

VAR_SAMPSyntax:VAR_SAMP(Expression or column)
Return value:FLOAT
Function type:Aggregate function, Window function
Function Description

The VAR_SAMP() function returns the sample variance 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 sample variance of the positions of the values ​​in their lists is calculated.

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

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

VAR_SAMP() can be used as a window function.

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

SQL Examples for the VAR_SAMP 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 var_samp(name) from test_table;

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

/* as Window function */

SELECT name, `type`, price, var_samp(price) 
  OVER (PARTITION BY `type`AS `var_samp` 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
var_samp(name)
double(23)
0
var_samp(num)
var_samp(price)
var_samp(`type`)
var_samp(`set`)
double(26)double(26)double(17)double(17)
1136.982162.6385271.428610.2679
var_samp(num)
var_samp(price)
double(26)double(26)
NULLNULL
name
type
price
var_samp
test_table
varchar(20) LATIN1
test_table
enum LATIN1
test_table
decimal(10,2)

double(26)
Art 6First1.5040.500000
Art 1First10.5040.500000
Art 3Second15.504.500000
Art 2Second12.504.500000
Art 4Third25.50282.031250
Art 7Third1.75282.031250
Art 8Last11.9020.480000
Art 5Last5.5020.480000
The examples were created with the MyWAY SQL manager: Download

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

The VAR_SAMP() function in MySQL and MariaDB is used to calculate the sample variance of a set of values of table columns or expressions in SQL queries and returns an estimate of the variance based on a sample or subset of the population of data. The function needs a sufficient number of data points to produce meaningful results. For only one data point or for an empty record, the function returns NULL.

Sample variance is used in statistical analysis to show the variability or spread of data. This allows you to estimate the variance of a larger population from a sample and how the sample data points differ from the sample mean. In hypothesis testing, this can be used to assess the significance of differences between groups or samples, and in conjunction with other statistical tests, the strength of evidence. In quality control, it can be used to monitor process variations. Tracking over time changes or trends can be identified that may indicate problems. Likewise, sample variance is used to assess risk and provides a measure of the volatility or variability of returns based on a sample of data. Higher variance values ​​mean higher risk.
UUID_SHORTUUID_TO_BINVAR_POPVAR_SAMPVARIANCEVERSIONWEEK

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

STDDEVSTDDEV(Expression or column)
More about STDDEV 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

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 VAR_SAMP SQL function: mysql.com and mariadb.com
Updated: 2023-09-17ImprintContactTerms & conditionsPrivacy & CookiesUpdatesSitemapFacebookLinkedinTwitterStatistics©2020-2024 Heino Cunze-Fischer