---
title: SQL aggregate functions
description: Aggregate data with SQL aggregate functions.
url: https://docs.influxdata.com/influxdb3/clustered/reference/sql/functions/aggregate/
estimated_tokens: 29555
product: InfluxDB Clustered
version: clustered
---

# SQL aggregate functions

SQL aggregate functions aggregate values in a specified column for each group or SQL partition and return a single row per group containing the aggregate value.

-   [General aggregate functions](#general-aggregate-functions)
    -   [array\_agg](#array_agg)
    -   [avg](#avg)
    -   [bit\_and](#bit_and)
    -   [bit\_or](#bit_or)
    -   [bit\_xor](#bit_xor)
    -   [bool\_and](#bool_and)
    -   [bool\_or](#bool_or)
    -   [count](#count)
    -   [first\_value](#first_value)
    -   [grouping](#grouping)
    -   [last\_value](#last_value)
    -   [max](#max)
    -   [mean](#mean)
    -   [median](#median)
    -   [min](#min)
    -   [nth\_value](#nth_value)
    -   [string\_agg](#string_agg)
    -   [sum](#sum)
-   [Statistical aggregate functions](#statistical-aggregate-functions)
    -   [corr](#corr)
    -   [covar](#covar)
    -   [covar\_pop](#covar_pop)
    -   [covar\_samp](#covar_samp)
    -   [regr\_avgx](#regr_avgx)
    -   [regr\_avgy](#regr_avgy)
    -   [regr\_count](#regr_count)
    -   [regr\_intercept](#regr_intercept)
    -   [regr\_r2](#regr_r2)
    -   [regr\_slope](#regr_slope)
    -   [regr\_sxx](#regr_sxx)
    -   [regr\_syy](#regr_syy)
    -   [regr\_sxy](#regr_sxy)
    -   [stddev](#stddev)
    -   [stddev\_pop](#stddev_pop)
    -   [stddev\_samp](#stddev_samp)
    -   [var](#var)
    -   [var\_pop](#var_pop)
    -   [var\_population](#var_population)
    -   [var\_samp](#var_samp)
    -   [var\_sample](#var_sample)
-   [Approximate aggregate functions](#approximate-aggregate-functions)
    -   [approx\_distinct](#approx_distinct)
    -   [approx\_median](#approx_median)
    -   [approx\_percentile\_cont](#approx_percentile_cont)
    -   [approx\_percentile\_cont\_with\_weight](#approx_percentile_cont_with_weight)

## General aggregate functions

-   [array\_agg](#array_agg)
-   [avg](#avg)
-   [bit\_and](#bit_and)
-   [bit\_or](#bit_or)
-   [bit\_xor](#bit_xor)
-   [bool\_and](#bool_and)
-   [bool\_or](#bool_or)
-   [count](#count)
-   [first\_value](#first_value)
-   [grouping](#grouping)
-   [last\_value](#last_value)
-   [max](#max)
-   [mean](#mean)
-   [median](#median)
-   [min](#min)
-   [nth\_value](#nth_value)
-   [string\_agg](#string_agg)
-   [sum](#sum)

### array\_agg

Returns an array created from the expression elements.

`array_agg` returns a `LIST` Arrow type. Use bracket notation to reference the index of an element in the returned array. Arrays are 1-indexed.

```sql
array_agg(expression)
```

#### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-array_agg-query-example)

View `array_agg` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  array_agg(temp)[3] AS '3rd_temp'
FROM home
GROUP BY room
```

| room | 3rd_temp |
| --- | --- |
| Kitchen | 22.7 |
| Living Room | 21.8 |

### avg

Returns the average of numeric values in the specified column.

```sql
avg(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

##### Aliases

-   `mean`

[](#view-avg-query-example)

View `avg` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  avg(precip) AS avg_precip
FROM weather
GROUP BY location
```

| location | avg_precip |
| --- | --- |
| Concord | 0.027120658135283374 |
| Hayward | 0.03708029197080292 |
| San Francisco | 0.03750912408759125 |

### bit\_and

Computes the bitwise `AND` of all non-null input values.

```sql
bit_and(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-bit_and-query-example)

View `bit_and` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  bit_and(precip::BIGINT) AS precip_bit_and
FROM weather
GROUP BY location
```

| location | precip_bit_and |
| --- | --- |
| Concord | 0 |
| Hayward | 0 |
| San Francisco | 0 |

### bit\_or

Computes the bitwise OR of all non-null input values.

```sql
bit_or(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-bit_or-query-example)

View `bit_or` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  bit_or(precip::BIGINT) AS precip_bit_or
FROM weather
GROUP BY location
```

| location | precip_bit_or |
| --- | --- |
| Concord | 7 |
| Hayward | 7 |
| San Francisco | 7 |

### bit\_xor

Computes the bitwise exclusive OR of all non-null input values.

```sql
bit_xor(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-bit_xor-query-example)

View `bit_xor` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  bit_xor(precip::BIGINT) AS precip_bit_xor
FROM weather
GROUP BY location
```

| location | precip_bit_xor |
| --- | --- |
| Concord | 4 |
| Hayward | 6 |
| San Francisco | 4 |

### bool\_and

Returns `true` if *all* non-null input values are `true`, otherwise returns `false`.

```sql
bool_and(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-bool_and-query-example)

View `bool_and` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  bool_and(precip > 0) AS precip_bool_and
FROM weather
GROUP BY location
```

| location | precip_bool_and |
| --- | --- |
| Concord | false |
| Hayward | false |
| San Francisco | false |

### bool\_or

Returns `true` if *any* non-null input value is `true`, otherwise returns `false`.

```sql
bool_or(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-bool_or-query-example)

View `bool_or` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  bool_or(precip > 0) AS precip_bool_or
FROM weather
GROUP BY location
```

| location | precip_bool_or |
| --- | --- |
| Concord | true |
| Hayward | true |
| San Francisco | true |

### count

Returns the number of rows in the specified column.

Count includes *null* values in the total count. To exclude *null* values from the total count, include `<column> IS NOT NULL` in the `WHERE` clause.

```sql
count(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-count-query-example)

View `count` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  count(precip) AS precip_count
FROM weather
GROUP BY location
```

| location | precip_count |
| --- | --- |
| Concord | 1094 |
| Hayward | 1096 |
| San Francisco | 1096 |

### first\_value

Returns the first element in an aggregation group according to the specified ordering. If no ordering is specified, returns an arbitrary element from the group.

```sql
first_value(expression [ORDER BY expression])
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-first_value-query-example)

View `first_value` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  first_value(temp_max ORDER BY time) AS temp_max_first_value
FROM weather
GROUP BY location
```

| location | temp_max_first_value |
| --- | --- |
| Concord | 59 |
| Hayward | 57 |
| San Francisco | 66 |

### grouping

Returns 1 if the data is aggregated across the specified column, or 0 if it is not aggregated in the result set.

```sql
grouping(expression)
```

##### Arguments

-   **expression**: Expression to evaluate whether data is aggregated across the specified column. Can be a constant, column, or function.

[](#view-grouping-query-example)

View `grouping` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  avg(temp_max) AS avg_max_temp,
  grouping(location) AS grouping
FROM weather
GROUP BY GROUPING SETS ((location), ())
```

| location | avg_max_temp | grouping |
| --- | --- | --- |
| Concord | 75.54379562043796 | 0 |
| Hayward | 69.12043795620438 | 0 |
| San Francisco | 67.59945255474453 | 0 |
|  | 70.75456204379562 | 1 |

### last\_value

Returns the last element in an aggregation group according to the specified ordering. If no ordering is specified, returns an arbitrary element from the group.

```sql
last_value(expression [ORDER BY expression])
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-last_value-query-example)

View `last_value` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  last_value(temp_max ORDER BY time) AS temp_max_last_value
FROM weather
GROUP BY location
```

| location | temp_max_last_value |
| --- | --- |
| Concord | 59 |
| Hayward | 58 |
| San Francisco | 62 |

### max

Returns the maximum value in the specified column.

```sql
max(expression)
```

*To return both the maximum value and its associated timestamp, use [`selector_max`](/influxdb3/clustered/reference/sql/functions/selector/#selector_max).*

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-max-query-example)

View `max` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  max(precip) AS max_precip
FROM weather
GROUP BY location
```

| location | max_precip |
| --- | --- |
| Concord | 4.53 |
| Hayward | 4.34 |
| San Francisco | 4.02 |

### mean

*Alias of [`avg`](#avg).*

### median

Returns the median value in the specified column.

```
median(expression)
```

#### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-median-query-example)

View `median` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  median(temp_avg) AS median_temp_avg
FROM weather
GROUP BY location
```

| location | median_temp_avg |
| --- | --- |
| Concord | 61.0 |
| Hayward | 59.0 |
| San Francisco | 58.0 |

### min

Returns the minimum value in the specified column.

```sql
min(expression)
```

*To return both the minimum value and its associated timestamp, use [`selector_max`](/influxdb3/clustered/reference/sql/functions/selector/#selector_min).*

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-min-query-example)

View `min` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  min(temp_min) AS min_temp_min
FROM weather
GROUP BY location
```

| location | min_temp_min |
| --- | --- |
| Concord | 28.0 |
| Hayward | 32.0 |
| San Francisco | 35.0 |

### nth\_value

Returns the nth value in a group of values.

```sql
nth_value(expression, n [ORDER BY order_expression_1, ... order_expression_n])
```

##### arguments

-   **expression**: The column or expression to retrieve the nth value from.
-   **n**: The position (nth) of the value to retrieve, based on the ordering.
-   **order\_expression\_1, … order\_expression\_n**: Expressions to order by. Can be a column or function, and any combination of arithmetic operators.

[](#view-nth_value-query-example)

View `nth_value` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  nth_value(temp, 3 ORDER BY time) AS "3rd_temp"
FROM
  home
GROUP BY
  room
```

| room | 3rd_temp |
| --- | --- |
| Living Room | 21.8 |
| Kitchen | 22.7 |

### string\_agg

Concatenates the values of string expressions and places separator values between them.

```sql
string_agg(expression, delimiter)
```

##### Arguments

-   **expression**: The string expression to concatenate. Can be a column or any valid string expression.
-   **delimiter**: A literal string to use as a separator between the concatenated values.

[](#view-string_agg-query-example)

View `string_agg` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT
  location,
  string_agg(temp_avg::STRING, ', ') AS string_agg
FROM 
  weather
WHERE
  time > '2020-01-01T00:00:00Z'
  AND time < '2020-01-05T00:00:00Z'
GROUP BY
  location
```

| location | string_agg |
| --- | --- |
| San Francisco | 54.0, 52.0, 54.0 |
| Hayward | 51.0, 50.0, 51.0 |
| Concord | 53.0, 49.0, 51.0 |

### sum

Returns the sum of all values in the specified column.

```sql
sum(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-sum-query-example)

View `sum` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  sum(precip) AS total_precip
FROM weather
GROUP BY location
```

| location | total_precip |
| --- | --- |
| Concord | 29.670000000000012 |
| Hayward | 40.64 |
| San Francisco | 41.110000000000014 |

## Statistical aggregate functions

-   [corr](#corr)
-   [covar](#covar)
-   [covar\_pop](#covar_pop)
-   [covar\_samp](#covar_samp)
-   [regr\_avgx](#regr_avgx)
-   [regr\_avgy](#regr_avgy)
-   [regr\_count](#regr_count)
-   [regr\_intercept](#regr_intercept)
-   [regr\_r2](#regr_r2)
-   [regr\_slope](#regr_slope)
-   [regr\_sxx](#regr_sxx)
-   [regr\_syy](#regr_syy)
-   [regr\_sxy](#regr_sxy)
-   [stddev](#stddev)
-   [stddev\_pop](#stddev_pop)
-   [stddev\_samp](#stddev_samp)
-   [var](#var)
-   [var\_pop](#var_pop)
-   [var\_population](#var_population)
-   [var\_samp](#var_samp)
-   [var\_sample](#var_sample)

### corr

Returns the coefficient of correlation between two numeric values.

```sql
corr(expression1, expression2)
```

##### Arguments

-   **expression1**: First column or literal value to operate on.
-   **expression2**: Second column or literal value to operate on.

[](#view-corr-query-example)

View `corr` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  corr(hum, temp) AS correlation
FROM home
GROUP BY room
```

| room | correlation |
| --- | --- |
| Living Room | 0.43665270457835725 |
| Kitchen | 0.6741766954929539 |

### covar

Returns the covariance of a set of number pairs.

```sql
covar(expression1, expression2)
```

##### Arguments

-   **expression1**: First column or literal value to operate on.
-   **expression2**: Second column or literal value to operate on.

[](#view-covar-query-example)

View `covar` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  covar(hum, temp) AS covar
FROM home
GROUP BY room
```

| room | covar |
| --- | --- |
| Living Room | 0.03346153846153959 |
| Kitchen | 0.11134615384615432 |

### covar\_pop

Returns the population covariance of a set of number pairs.

```sql
covar_pop(expression1, expression2)
```

##### Arguments

-   **expression1**: First column or literal value to operate on.
-   **expression2**: Second column or literal value to operate on.

[](#view-covar_pop-query-example)

View `covar_pop` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  covar_pop(hum, temp) AS covar_pop
FROM home
GROUP BY room
```

| room | covar_pop |
| --- | --- |
| Kitchen | 0.10278106508875783 |
| Living Room | 0.030887573964498087 |

### covar\_samp

Returns the sample covariance of a set of number pairs.

```sql
covar_samp(expression1, expression2)
```

##### Arguments

-   **expression1**: First column or literal value to operate on.
-   **expression2**: Second column or literal value to operate on.

[](#view-covar_samp-query-example)

View `covar_samp` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  covar_samp(hum, temp) AS covar_samp
FROM home
GROUP BY room
```

| room | covar_samp |
| --- | --- |
| Kitchen | 0.11134615384615432 |
| Living Room | 0.03346153846153959 |

### regr\_avgx

Computes the average of the independent variable (input), `expression_x`, for the non-null dependent variable, `expression_y`.

```sql
regr_avgx(expression_y, expression_x)
```

##### Arguments

-   **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-regr_avgx-query-example)

View `regr_avgx` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  regr_avgx(temp_min, temp_max) AS temp_regr_avgx
FROM weather
GROUP BY location
```

| location | temp_regr_avgx |
| --- | --- |
| Concord | 75.54379562043796 |
| Hayward | 69.14808043875686 |
| San Francisco | 67.59945255474454 |

### regr\_avgy

Computes the average of the dependent variable (output), `expression_y`, for the non-null dependent variable, `expression_y`.

```sql
regr_avgy(expression_y, expression_x)
```

##### Arguments

-   **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-regr_avgy-query-example)

View `regr_avgy` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  regr_avgy(temp_min, temp_max) AS temp_regr_avgy
FROM weather
GROUP BY location
```

| location | temp_regr_avgy |
| --- | --- |
| Concord | 50.153284671532845 |
| Hayward | 50.913162705667276 |
| San Francisco | 51.52372262773722 |

### regr\_count

Counts the number of non-null paired data points.

```sql
regr_count(expression_y, expression_x)
```

##### Arguments

-   **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-regr_count-query-example)

View `regr_count` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  regr_count(temp_min, temp_max) AS temp_regr_count
FROM weather
GROUP BY location
```

| location | temp_regr_count |
| --- | --- |
| Concord | 1096 |
| Hayward | 1094 |
| San Francisco | 1096 |

### regr\_intercept

Computes the y-intercept of the linear regression line. For the equation `(y = kx + b)`, this function returns `b`.

```sql
regr_intercept(expression_y, expression_x)
```

##### Arguments

-   **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-regr_intercept-query-example)

View `regr_intercept` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  regr_intercept(temp_min, temp_max) AS temp_regr_intercept
FROM weather
GROUP BY location
```

| location | temp_regr_intercept |
| --- | --- |
| Concord | 11.636281392206769 |
| Hayward | 12.876956842745152 |
| San Francisco | 19.125237647086607 |

### regr\_r2

Computes the square of the correlation coefficient between the independent and dependent variables.

```sql
regr_r2(expression_y, expression_x)
```

##### Arguments

-   **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-regr_r2-query-example)

View `regr_r2` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  regr_r2(temp_min, temp_max) AS temp_regr_r2
FROM weather
GROUP BY location
```

| location | temp_regr_r2 |
| --- | --- |
| Concord | 0.6474628308450441 |
| Hayward | 0.5166296626320914 |
| San Francisco | 0.5032317511200297 |

### regr\_slope

Returns the slope of the linear regression line for non-null pairs in aggregate columns. Given input column `Y` and `X`: `regr_slope(Y, X)` returns the slope (`k` in `Y = k*X + b`) using minimal RSS fitting.

```sql
regr_slope(expression_y, expression_x)
```

##### Arguments

-   **expression\_y**: Y expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **expression\_x**: X expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-regr_slope-query-example)

View `regr_slope` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  regr_slope(temp_min, temp_max) AS temp_regr_slope
FROM weather
GROUP BY location
```

| location | temp_regr_slope |
| --- | --- |
| Concord | 0.5098632252058237 |
| Hayward | 0.5500688612261629 |
| San Francisco | 0.4792714105844738 |

### regr\_sxx

Computes the sum of squares of the independent variable.

```sql
regr_sxx(expression_y, expression_x)
```

##### Arguments

-   **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-regr_sxx-query-example)

View `regr_sxx` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  regr_sxx(temp_min, temp_max) AS temp_regr_sxx
FROM weather
GROUP BY location
```

| location | temp_regr_sxx |
| --- | --- |
| Concord | 210751.89781021897 |
| Hayward | 99644.01096892142 |
| San Francisco | 77413.15967153282 |

### regr\_syy

Computes the sum of squares of the dependent variable.

```sql
regr_syy(expression_y, expression_x)
```

##### Arguments

-   **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-regr_syy-query-example)

View `regr_syy` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  regr_syy(temp_min, temp_max) AS temp_regr_syy
FROM weather
GROUP BY location
```

| location | temp_regr_syy |
| --- | --- |
| Concord | 84618.24817518248 |
| Hayward | 58358.750457038404 |
| San Francisco | 35335.38321167884 |

### regr\_sxy

Computes the sum of products of paired data points.

```sql
regr_sxy(expression_y, expression_x)
```

#### Arguments

-   **expression\_y**: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **expression\_x**: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-regr_sxy-query-example)

View `regr_sxy` query example

*The following example uses the [NOAA Bay Area weather data](/influxdb3/clustered/reference/sample-data/#noaa-bay-area-weather-data).*

```sql
SELECT 
  location,
  regr_sxy(temp_min, temp_max) AS temp_regr_sxy
FROM weather
GROUP BY location
```

| location | temp_regr_sxy |
| --- | --- |
| Concord | 107454.64233576645 |
| Hayward | 54811.06764168191 |
| San Francisco | 37101.914233576645 |

### stddev

Returns the standard deviation of a set of numbers.

```sql
stddev(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-stddev-query-example)

View `stddev` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  stddev(co) AS stddev
FROM home
GROUP BY room
```

| room | stddev |
| --- | --- |
| Living Room | 5.885662718931967 |
| Kitchen | 9.321879418735037 |

### stddev\_pop

Returns the population standard deviation of a set of numbers.

```sql
stddev_pop(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-stddev_pop-query-example)

View `stddev_pop` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  stddev_pop(co) AS stddev_pop
FROM home
GROUP BY room
```

| room | stddev_pop |
| --- | --- |
| Kitchen | 8.956172047894082 |
| Living Room | 5.654761830612032 |

### stddev\_samp

Returns the sample standard deviation of a set of numbers.

```sql
stddev_samp(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-stddev_samp-query-example)

View `stddev_samp` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  stddev_samp(co) AS stddev_samp
FROM home
GROUP BY room
```

| room | stddev_samp |
| --- | --- |
| Living Room | 5.885662718931967 |
| Kitchen | 9.321879418735037 |

### var

Returns the statistical variance of a set of numbers.

```sql
var(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-var-query-example)

View `var` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  var(co) AS var
FROM home
GROUP BY room
```

| room | var |
| --- | --- |
| Living Room | 34.64102564102564 |
| Kitchen | 86.89743589743587 |

### var\_pop

Returns the statistical population variance of a set of numbers.

```sql
var_pop(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

##### Aliases

-   var\_population

[](#view-var_pop-query-example)

View `var_pop` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  var_pop(co) AS var_pop
FROM home
GROUP BY room
```

| room | var_pop |
| --- | --- |
| Living Room | 31.976331360946745 |
| Kitchen | 80.21301775147927 |

### var\_population

*Alias of [`var_pop`](#var_pop).*

### var\_samp

Returns the statistical sample variance of a set of numbers.

```sql
var_samp(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

##### Aliases

-   var\_sample

[](#view-var_samp-query-example)

View `var_samp` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  var_samp(co) AS var_samp
FROM home
GROUP BY room
```

| room | var_samp |
| --- | --- |
| Kitchen | 86.89743589743587 |
| Living Room | 34.64102564102564 |

### var\_sample

*Alias of [var\_samp](#var_samp).*

## Approximate aggregate functions

-   [approx\_distinct](#approx_distinct)
-   [approx\_median](#approx_median)
-   [approx\_percentile\_cont](#approx_percentile_cont)
-   [approx\_percentile\_cont\_with\_weight](#approx_percentile_cont_with_weight)

### approx\_distinct

Returns the approximate number of distinct input values calculated using the HyperLogLog algorithm.

```sql
approx_distinct(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-approx_distinct-query-example)

View `approx_distinct` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  approx_distinct(co::string) AS approx_distinct
FROM home
GROUP BY room
```

| room | approx_distinct |
| --- | --- |
| Living Room | 7 |
| Kitchen | 8 |

### approx\_median

Returns the approximate median (50th percentile) of input values. It is an alias of `approx_percentile_cont(0.5) WITHIN GROUP (ORDER BY expression)`.

```sql
approx_median(expression)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-approx_median-query-example)

View `approx_median` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  approx_median(temp) AS approx_median
FROM home
GROUP BY room
```

| room | approx_median |
| --- | --- |
| Kitchen | 22.7 |
| Living Room | 22.3 |

### approx\_percentile\_cont

Returns the approximate percentile of input values using the t-digest algorithm.

```sql
approx_percentile_cont(percentile [, centroids]) WITHIN GROUP (ORDER BY expression)
-- OR
approx_percentile_cont(expression, percentile, centroids)
```

##### Arguments

-   **percentile**: Percentile to compute. Must be a float value between 0 and 1 (inclusive).
    
-   **centroids**: Number of centroids to use in the t-digest algorithm. *Default is 100*. A higher number results in more accurate approximation but requires more memory.
    
-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
    
    If there are this number or fewer unique values, you can expect an exact result. A higher number of centroids results in a more accurate approximation, but requires more memory to compute.
    

[](#view-approx_percentile_cont-query-example)

View `approx_percentile_cont` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  approx_percentile_cont(0.99) WITHIN GROUP (ORDER BY temp) AS "99th_percentile"
FROM home
GROUP BY room
```

| room | 99th_percentile |
| --- | --- |
| Kitchen | 23.3 |
| Living Room | 22.8 |

### approx\_percentile\_cont\_with\_weight

Returns the weighted approximate percentile of input values using the t-digest algorithm.

```sql
approx_percentile_cont_with_weight(weight, percentile [, centroids]) WITHIN GROUP (ORDER BY expression)
-- OR
approx_percentile_cont_with_weight(expression, weight, percentile)
```

##### Arguments

-   **expression**: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **weight**: Expression to use as weight. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **percentile**: Percentile to compute. Must be a float value between 0 and 1 (inclusive).
-   **centroids**: Number of centroids to use in the t-digest algorithm. *Default is 100.* A higher number results in more accurate approximation but requires more memory.

[](#view-approx_percentile_cont_with_weight-query-example)

View `approx_percentile_cont_with_weight` query example

*The following example uses the [Get started home sensor sample data](/influxdb3/clustered/reference/sample-data/#get-started-home-sensor-data).*

```sql
SELECT
  room,
  approx_percentile_cont_with_weight(co, 0.99) WITHIN GROUP (ORDER BY temp) AS "co_weighted_99th_percentile"
FROM home
GROUP BY room
```

| room | co_weighted_99th_percentile |
| --- | --- |
| Kitchen | 23.3 |
| Living Room | 22.8 |

#### Related

-   [Aggregate data with SQL](/influxdb3/clustered/query-data/sql/aggregate-select/)