## 9.3. Statistical Aggregate Functions

### 9.3.1. `CORR()`

Correlation coefficient

Result type`DOUBLE PRECISION`

Syntax

```  |`CORR ( <expr1>, <expr2> )`
```

Table 9.7`CORR` Function Parameters
ParameterDescription

exprN

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The `CORR` function return the correlation coefficient for a pair of numerical expressions.

The function `CORR(<expr1>, <expr2>)` is equivalent to

```  |`COVAR_POP(<expr1>, <expr2>) / (STDDEV_POP(<expr2>) * STDDEV_POP(<expr1>))`
```

This is also known as the Pearson correlation coefficient.

In a statistical sense, correlation is the degree to which a pair of variables are linearly related. A linear relation between variables means that the value of one variable can to a certain extent predict the value of the other. The correlation coefficient represents the degree of correlation as a number ranging from -1 (high inverse correlation) to 1 (high correlation). A value of 0 corresponds to no correlation.

If the group or window is empty, or contains only `NULL` values, the result will be `NULL`.

#### 9.3.1.1. `CORR` Examples

```  |`select`
|`  corr(alength, aheight) AS c_corr`
|`from measure`
```

### 9.3.2. `COVAR_POP()`

Population covariance

Result type`DOUBLE PRECISION`

Syntax

```  |`COVAR_POP ( <expr1>, <expr2> )`
```

Table 9.8`COVAR_POP` Function Parameters
ParameterDescription

exprN

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The function `COVAR_POP` returns the population covariance for a pair of numerical expressions.

The function `COVAR_POP(<expr1>, <expr2>)` is equivalent to

```  |`(SUM(<expr1> * <expr2>) - SUM(<expr1>) * SUM(<expr2>) / COUNT(*)) / COUNT(*)`
```

If the group or window is empty, or contains only `NULL` values, the result will be `NULL`.

#### 9.3.2.1. `COVAR_POP` Examples

```  |`select`
|`  covar_pop(alength, aheight) AS c_covar_pop`
|`from measure`
```

### 9.3.3. `COVAR_SAMP()`

Sample covariance

Result type`DOUBLE PRECISION`

Syntax

```  |`COVAR_SAMP ( <expr1>, <expr2> )`
```

Table 9.9`COVAR_SAMP` Function Parameters
ParameterDescription

exprN

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The function `COVAR_SAMP` returns the sample covariance for a pair of numerical expressions.

The function `COVAR_SAMP(<expr1>, <expr2>)` is equivalent to

```  |`(SUM(<expr1> * <expr2>) - SUM(<expr1>) * SUM(<expr2>) / COUNT(*)) / (COUNT(*) - 1)`
```

If the group or window is empty, contains only 1 row, or contains only `NULL` values, the result will be `NULL`.

#### 9.3.3.1. `COVAR_SAMP` Examples

```  |`select`
|`  covar_samp(alength, aheight) AS c_covar_samp`
|`from measure`
```

### 9.3.4. `STDDEV_POP()`

Population standard deviation

Result type`DOUBLE PRECISION` or `NUMERIC` depending on the type of expr

Syntax

```  |`STDDEV_POP ( <expr> )`
```

Table 9.10`STDDEV_POP` Function Parameters
ParameterDescription

expr

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The function `STDDEV_POP` returns the population standard deviation for a group or window. `NULL` values are skipped.

The function `STDDEV_POP(<expr>)` is equivalent to

```  |`SQRT(VAR_POP(<expr>))`
```

If the group or window is empty, or contains only `NULL` values, the result will be `NULL`.

#### 9.3.4.1. `STDDEV_POP` Examples

```  |`select`
|`  dept_no`
|`  stddev_pop(salary)`
|`from employee`
|`group by dept_no`
```

### 9.3.5. `STDDEV_SAMP()`

Sample standard deviation

Result type`DOUBLE PRECISION` or `NUMERIC` depending on the type of expr

Syntax

```  |`STDDEV_POP ( <expr> )`
```

Table 9.11`STDDEV_SAMP` Function Parameters
ParameterDescription

expr

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The function `STDDEV_SAMP` returns the sample standard deviation for a group or window. `NULL` values are skipped.

The function `STDDEV_SAMP(<expr>)` is equivalent to

```  |`SQRT(VAR_SAMP(<expr>))`
```

If the group or window is empty, contains only 1 row, or contains only `NULL` values, the result will be `NULL`.

#### 9.3.5.1. `STDDEV_SAMP` Examples

```  |`select`
|`  dept_no`
|`  stddev_samp(salary)`
|`from employee`
|`group by dept_no`
```

### 9.3.6. `VAR_POP()`

Population variance

Result type`DOUBLE PRECISION` or `NUMERIC` depending on the type of expr

Syntax

```  |`VAR_POP ( <expr> )`
```

Table 9.12`VAR_POP` Function Parameters
ParameterDescription

expr

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The function `VAR_POP` returns the population variance for a group or window. `NULL` values are skipped.

The function `VAR_POP(<expr>)` is equivalent to

```  |`(SUM(<expr> * <expr>) - SUM (<expr>) * SUM (<expr>) / COUNT(<expr>))`
|`  / COUNT (<expr>)`
```

If the group or window is empty, or contains only `NULL` values, the result will be `NULL`.

#### 9.3.6.1. `VAR_POP` Examples

```  |`select`
|`  dept_no`
|`  var_pop(salary)`
|`from employee`
|`group by dept_no`
```

### 9.3.7. `VAR_SAMP()`

Sample variance

Result type`DOUBLE PRECISION` or `NUMERIC` depending on the type of expr

Syntax

```  |`VAR_SAMP ( <expr> )`
```

Table 9.13`VAR_SAMP` Function Parameters
ParameterDescription

expr

Numeric expression. It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF. Aggregate functions are not allowed as expressions.

The function `VAR_POP` returns the sample variance for a group or window. `NULL` values are skipped.

The function `VAR_SAMP(<expr>)` is equivalent to

```  |`(SUM(<expr> * <expr>) - SUM(<expr>) * SUM (<expr>) / COUNT (<expr>))`
|`  / (COUNT(<expr>) - 1)`
```

If the group or window is empty, contains only 1 row, or contains only `NULL` values, the result will be `NULL`.

#### 9.3.7.1. `VAR_SAMP` Examples

```  |`select`
|`  dept_no`
|`  var_samp(salary)`
|`from employee`
|`group by dept_no`
```