Documentation

InfluxQL aggregate functions

Use aggregate functions to assess, aggregate, and return values in your data. Aggregate functions return one row containing the aggregate values from each InfluxQL group.

Each aggregate function below covers syntax including parameters to pass to the function, and examples of how to use the function. Examples use NOAA water sample data.

COUNT()

Returns the number of non-null field values. Supports all field value data types.

Syntax

SELECT COUNT( [ * | <field_key> | /<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

COUNT(*)

Returns the number of field values associated with each field key in the measurement.

COUNT(field_key)

Returns the number of field values associated with the field key.

COUNT(/regular_expression/)

Returns the number of field values associated with each field key that matches the regular expression.

Examples

Count values for a field

Count values for each field in a measurement

Count the values that match a regular expression

Count distinct values for a field

DISTINCT()

Returns the list of unique field values. Supports all field value data types.

InfluxQL supports nesting DISTINCT() with COUNT().

Syntax

SELECT DISTINCT( [ <field_key> | /<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

DISTINCT(field_key)

Returns the unique field values associated with the field key.

Examples

List the distinct field values associated with a field key

List the distinct field values associated with each field key in a measurement

-->

INTEGRAL()

Returns the area under the curve for subsequent field values.

INTEGRAL() does not support fill(). INTEGRAL() supports int64 and float64 field value data types.

Syntax

SELECT INTEGRAL( [ * | <field_key> | /<regular_expression>/ ] [ , <unit> ]  ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

InfluxDB calculates the area under the curve for subsequent field values and converts those results into the summed area per unit. The unit argument is an integer followed by an optional duration literal. If the query does not specify the unit, the unit defaults to one second (1s).

INTEGRAL(field_key)

Returns the area under the curve for subsequent field values associated with the field key.

INTEGRAL(/regular_expression/)

Returns the area under the curve for subsequent field values associated with each field key that matches the regular expression.

INTEGRAL(*)

Returns the average field value associated with each field key in the measurement.

Examples

The following examples use a subset of the NOAA water sample data data:

SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet

time water_level
2019-08-18T00:00:00Z 2.3520000000
2019-08-18T00:06:00Z 2.3790000000
2019-08-18T00:12:00Z 2.3430000000
2019-08-18T00:18:00Z 2.3290000000
2019-08-18T00:24:00Z 2.2640000000
2019-08-18T00:30:00Z 2.2670000000

Calculate the integral for the field values associated with a field key

Calculate the integral for the field values associated with a field key and specify the unit option

Calculate the integral for the field values associated with each field key in a measurement and specify the unit option

Calculate the integral for the field values associated with each field key that matches a regular expression and specify the unit option

Calculate the integral for the field values associated with a field key and include several clauses

MEAN()

Returns the arithmetic mean (average) of field values. MEAN() supports int64 and float64 field value data types.

Syntax

SELECT MEAN( [ * | <field_key> | /<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

MEAN(field_key) Returns the average field value associated with the field key.

`MEAN(/regular_expression/)

Returns the average field value associated with each field key that matches the regular expression.

MEAN(*) Returns the average field value associated with each field key in the measurement.

Examples

Calculate the mean field value associated with a field key

Calculate the mean field value associated with each field key in a measurement

Calculate the mean field value associated with each field key that matches a regular expression

Calculate the mean field value associated with a field key and include several clauses

MEDIAN()

Returns the middle value from a sorted list of field values. MEDIAN() supports int64 and float64 field value data types.

Note: MEDIAN() is nearly equivalent to PERCENTILE(field_key, 50), except MEDIAN() returns the average of the two middle field values if the field contains an even number of values.

Syntax

SELECT MEDIAN( [ * | <field_key> | /<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

MEDIAN(field_key)

Returns the middle field value associated with the field key.

MEDIAN(/regular_expression/)

Returns the middle field value associated with each field key that matches the regular expression.

MEDIAN(*)

Returns the middle field value associated with each field key in the measurement.

Examples

Calculate the median field value associated with a field key

Calculate the median field value associated with each field key in a measurement

Calculate the median field value associated with each field key that matches a regular expression

Calculate the median field value associated with a field key and include several clauses

MODE()

Returns the most frequent value in a list of field values. MODE() supports all field value data types.

Note: MODE() returns the field value with the earliest timestamp if there’s a tie between two or more values for the maximum number of occurrences.

Syntax

SELECT MODE( [ * | <field_key> | /<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

MODE(field_key)

Returns the most frequent field value associated with the field key.

MODE(/regular_expression/)

Returns the most frequent field value associated with each field key that matches the regular expression.

MODE(*)

Returns the most frequent field value associated with each field key in the measurement.

Examples

Calculate the mode field value associated with a field key

Calculate the mode field value associated with each field key in a measurement

Calculate the mode field value associated with each field key that matches a regular expression

Calculate the mode field value associated with a field key and include several clauses

SPREAD()

Returns the difference between the minimum and maximum field values. SPREAD() supports int64 and float64 field value data types.

Syntax

SELECT SPREAD( [ * | <field_key> | /<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

SPREAD(field_key)

Returns the difference between the minimum and maximum field values associated with the field key.

SPREAD(/regular_expression/)

Returns the difference between the minimum and maximum field values associated with each field key that matches the regular expression.

SPREAD(*)

Returns the difference between the minimum and maximum field values associated with each field key in the measurement.

Examples

Calculate the spread for the field values associated with a field key

Calculate the spread for the field values associated with each field key in a measurement

Calculate the spread for the field values associated with each field key that matches a regular expression

Calculate the spread for the field values associated with a field key and include several clauses

STDDEV()

Returns the standard deviation of field values. STDDEV() supports int64 and float64 field value data types.

Syntax

SELECT STDDEV( [ * | <field_key> | /<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

STDDEV(field_key)

Returns the standard deviation of field values associated with the field key.

STDDEV(/regular_expression/)

Returns the standard deviation of field values associated with each field key that matches the regular expression.

STDDEV(*)

Returns the standard deviation of field values associated with each field key in the measurement.

Examples

Calculate the standard deviation for the field values associated with a field key

Calculate the standard deviation for the field values associated with each field key in a measurement

Calculate the standard deviation for the field values associated with each field key that matches a regular expression

Calculate the standard deviation for the field values associated with a field key and include several clauses

SUM()

Returns the sum of field values. SUM() supports int64 and float64 field value data types.

Syntax

SELECT SUM( [ * | <field_key> | /<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

SUM(field_key)

Returns the sum of field values associated with the field key.

SUM(/regular_expression/)

Returns the sum of field values associated with each field key that matches the regular expression.

SUM(*)

Returns the sums of field values associated with each field key in the measurement.

Examples

Calculate the sum of the field values associated with a field key

Calculate the sum of the field values associated with each field key in a measurement

Calculate the sum of the field values associated with each field key that matches a regular expression

Calculate the sum of the field values associated with a field key and include several clauses


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:

State of the InfluxDB Cloud Serverless documentation

InfluxDB Cloud Serverless documentation is a work in progress.

The new documentation for InfluxDB Cloud Serverless is a work in progress. We are adding new information and content almost daily. Thank you for your patience!

If there is specific information you’re looking for, please submit a documentation issue.

InfluxDB Cloud powered by TSM