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
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
Was this page helpful?
Thank you for your feedback!
Support and feedback
Thank you for being part of our community! We welcome and encourage your feedback and bug reports for InfluxDB and this documentation. To find support, use the following resources:
InfluxDB Cloud and InfluxDB Enterprise customers can contact InfluxData Support.