# Statistical aggregate functions

BigQuery supports the following statistical aggregate functions.

### CORR

``````CORR(
X1, X2
)
[OVER (...)]
``````

Description

Returns the Pearson coefficient of correlation of a set of number pairs. For each number pair, the first number is the dependent variable and the second number is the independent variable. The return result is between `-1` and `1`. A result of `0` indicates no correlation.

All numeric types are supported. If the input is `NUMERIC` or `BIGNUMERIC` then the internal aggregation is stable with the final output converted to a `FLOAT64`. Otherwise the input is converted to a `FLOAT64` before aggregation, resulting in a potentially unstable result.

This function ignores any input pairs that contain one or more NULL values. If there are fewer than two input pairs without NULL values, this function returns NULL.

Optional Clause

`OVER`: Specifies a window. See Analytic Functions.

Return Data Type

`FLOAT64`

### COVAR_POP

``````COVAR_POP(
X1, X2
)
[OVER (...)]
``````

Description

Returns the population covariance of a set of number pairs. The first number is the dependent variable; the second number is the independent variable. The return result is between `-Inf` and `+Inf`.

All numeric types are supported. If the input is `NUMERIC` or `BIGNUMERIC` then the internal aggregation is stable with the final output converted to a `FLOAT64`. Otherwise the input is converted to a `FLOAT64` before aggregation, resulting in a potentially unstable result.

This function ignores any input pairs that contain one or more NULL values. If there is no input pair without NULL values, this function returns NULL. If there is exactly one input pair without NULL values, this function returns 0.

Optional Clause

`OVER`: Specifies a window. See Analytic Functions.

Return Data Type

`FLOAT64`

### COVAR_SAMP

``````COVAR_SAMP(
X1, X2
)
[OVER (...)]
``````

Description

Returns the sample covariance of a set of number pairs. The first number is the dependent variable; the second number is the independent variable. The return result is between `-Inf` and `+Inf`.

All numeric types are supported. If the input is `NUMERIC` or `BIGNUMERIC` then the internal aggregation is stable with the final output converted to a `FLOAT64`. Otherwise the input is converted to a `FLOAT64` before aggregation, resulting in a potentially unstable result.

This function ignores any input pairs that contain one or more NULL values. If there are fewer than two input pairs without NULL values, this function returns NULL.

Optional Clause

`OVER`: Specifies a window. See Analytic Functions.

Return Data Type

`FLOAT64`

### STDDEV_POP

``````STDDEV_POP(
[DISTINCT]
expression
)
[OVER (...)]
``````

Description

Returns the population (biased) standard deviation of the values. The return result is between `0` and `+Inf`.

All numeric types are supported. If the input is `NUMERIC` or `BIGNUMERIC` then the internal aggregation is stable with the final output converted to a `FLOAT64`. Otherwise the input is converted to a `FLOAT64` before aggregation, resulting in a potentially unstable result.

This function ignores any NULL inputs. If all inputs are ignored, this function returns NULL.

If this function receives a single non-NULL input, it returns `0`.

Optional Clauses

The clauses are applied in the following order:

1. `OVER`: Specifies a window. See Analytic Functions. This clause is currently incompatible with all other clauses within `STDDEV_POP()`.
2. `DISTINCT`: Each distinct value of `expression` is aggregated only once into the result.

Return Data Type

`FLOAT64`

### STDDEV_SAMP

``````STDDEV_SAMP(
[DISTINCT]
expression
)
[OVER (...)]
``````

Description

Returns the sample (unbiased) standard deviation of the values. The return result is between `0` and `+Inf`.

All numeric types are supported. If the input is `NUMERIC` or `BIGNUMERIC` then the internal aggregation is stable with the final output converted to a `FLOAT64`. Otherwise the input is converted to a `FLOAT64` before aggregation, resulting in a potentially unstable result.

This function ignores any NULL inputs. If there are fewer than two non-NULL inputs, this function returns NULL.

Optional Clauses

The clauses are applied in the following order:

1. `OVER`: Specifies a window. See Analytic Functions. This clause is currently incompatible with all other clauses within `STDDEV_SAMP()`.
2. `DISTINCT`: Each distinct value of `expression` is aggregated only once into the result.

Return Data Type

`FLOAT64`

### STDDEV

``````STDDEV(
[DISTINCT]
expression
)
[OVER (...)]
``````

Description

An alias of STDDEV_SAMP.

### VAR_POP

``````VAR_POP(
[DISTINCT]
expression
)
[OVER (...)]
``````

Description

Returns the population (biased) variance of the values. The return result is between `0` and `+Inf`.

All numeric types are supported. If the input is `NUMERIC` or `BIGNUMERIC` then the internal aggregation is stable with the final output converted to a `FLOAT64`. Otherwise the input is converted to a `FLOAT64` before aggregation, resulting in a potentially unstable result.

This function ignores any NULL inputs. If all inputs are ignored, this function returns NULL.

If this function receives a single non-NULL input, it returns `0`.

Optional Clauses

The clauses are applied in the following order:

1. `OVER`: Specifies a window. See Analytic Functions. This clause is currently incompatible with all other clauses within `VAR_POP()`.
2. `DISTINCT`: Each distinct value of `expression` is aggregated only once into the result.

Return Data Type

`FLOAT64`

### VAR_SAMP

``````VAR_SAMP(
[DISTINCT]
expression
)
[OVER (...)]
``````

Description

Returns the sample (unbiased) variance of the values. The return result is between `0` and `+Inf`.

All numeric types are supported. If the input is `NUMERIC` or `BIGNUMERIC` then the internal aggregation is stable with the final output converted to a `FLOAT64`. Otherwise the input is converted to a `FLOAT64` before aggregation, resulting in a potentially unstable result.

This function ignores any NULL inputs. If there are fewer than two non-NULL inputs, this function returns NULL.

Optional Clauses

The clauses are applied in the following order:

1. `OVER`: Specifies a window. See Analytic Functions. This clause is currently incompatible with all other clauses within `VAR_SAMP()`.
2. `DISTINCT`: Each distinct value of `expression` is aggregated only once into the result.

Return Data Type

`FLOAT64`

### VARIANCE

``````VARIANCE(
[DISTINCT]
expression
)
[OVER (...)]
``````

Description

An alias of VAR_SAMP.

[{ "type": "thumb-down", "id": "hardToUnderstand", "label":"Hard to understand" },{ "type": "thumb-down", "id": "incorrectInformationOrSampleCode", "label":"Incorrect information or sample code" },{ "type": "thumb-down", "id": "missingTheInformationSamplesINeed", "label":"Missing the information/samples I need" },{ "type": "thumb-down", "id": "otherDown", "label":"Other" }]
[{ "type": "thumb-up", "id": "easyToUnderstand", "label":"Easy to understand" },{ "type": "thumb-up", "id": "solvedMyProblem", "label":"Solved my problem" },{ "type": "thumb-up", "id": "otherUp", "label":"Other" }]