Documentation

SQL aggregate functions

Limited availability

InfluxDB Clustered is currently only available to a limited group of InfluxData customers. If interested in being part of the limited access group, please contact the InfluxData Sales team.

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

array_agg

Returns an array created from the expression elements.

array_agg returns a LIST arrow type which is not supported by InfluxDB. To use with InfluxDB, use bracket notation to reference the index of an element in the returned array. Arrays are 1-indexed.

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

avg

Returns the average of numeric values in the specified column.

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

bit_and

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

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

bit_or

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

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

bit_xor

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

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

bool_and

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

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

bool_or

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

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

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.

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

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.

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

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.

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

max

Returns the maximum value in the specified column.

max(expression)

To return both the maximum value and its associated timestamp, use 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

mean

Alias of 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

min

Returns the minimum value in the specified column.

min(expression)

To return both the minimum value and its associated timestamp, use selector_max.

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

View min query example

sum

Returns the sum of all values in the specified column.

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

Statistical aggregate functions

corr

Returns the coefficient of correlation between two numeric values.

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

covar

Returns the covariance of a set of number pairs.

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

covar_pop

Returns the population covariance of a set of number pairs.

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

covar_samp

Returns the sample covariance of a set of number pairs.

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

regr_avgx

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

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

regr_avgy

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

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

regr_count

Counts the number of non-null paired data points.

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

regr_intercept

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

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

regr_r2

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

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

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.

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

regr_sxx

Computes the sum of squares of the independent variable.

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

regr_syy

Computes the sum of squares of the dependent variable.

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

regr_sxy

Computes the sum of products of paired data points.

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

stddev

Returns the standard deviation of a set of numbers.

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

stddev_pop

Returns the population standard deviation of a set of numbers.

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

stddev_samp

Returns the sample standard deviation of a set of numbers.

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

var

Returns the statistical variance of a set of numbers.

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

var_pop

Returns the statistical population variance of a set of numbers.

var_pop(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

View var_pop query example

var_samp

Returns the statistical sample variance of a set of numbers.

var_samp(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

View var_samp query example

Approximate aggregate functions

approx_distinct

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

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

approx_median

Returns the approximate median (50th percentile) of input values. It is an alias of approx_percentile_cont(x, 0.5).

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

approx_percentile_cont

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

approx_percentile_cont(expression, percentile, centroids)
Arguments
  • expression: Expression to operate on. 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.

    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

approx_percentile_cont_with_weight

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

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).

View approx_percentile_cont_with_weight query example


Was this page helpful?

Thank you for your feedback!


Introducing InfluxDB Clustered

A highly available InfluxDB 3.0 cluster on your own infrastructure.

InfluxDB Clustered is a highly available InfluxDB 3.0 cluster built for high write and query workloads on your own infrastructure.

InfluxDB Clustered is currently in limited availability and is only available to a limited group of InfluxData customers. If interested in being part of the limited access group, please contact the InfluxData Sales team.

Learn more
Contact InfluxData Sales

The future of Flux

Flux is going into maintenance mode. You can continue using it as you currently are without any changes to your code.

Flux is going into maintenance mode and will not be supported in InfluxDB 3.0. This was a decision based on the broad demand for SQL and the continued growth and adoption of InfluxQL. We are continuing to support Flux for users in 1.x and 2.x so you can continue using it with no changes to your code. If you are interested in transitioning to InfluxDB 3.0 and want to future-proof your code, we suggest using InfluxQL.

For information about the future of Flux, see the following: