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

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

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.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!


Set your InfluxDB URL

Upgrade to InfluxDB Cloud or InfluxDB 2.0!

InfluxDB Cloud and InfluxDB OSS 2.0 ready for production.