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.
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
Return the number of non-null field values in the water_level field key in the h2o_feet measurement.
SELECTCOUNT("water_level")FROM"h2o_feet"
name: h2o_feet
time
count
1970-01-01T00:00:00Z
61026.0000000000
Count values for each field in a measurement
Return the number of non-null field values for each field key associated with the h2o_feet measurement.
The h2o_feet measurement has two field keys: level description and water_level.
SELECTCOUNT(*)FROM"h2o_feet"
name: h2o_feet
time
count_level description
count_water_level
1970-01-01T00:00:00Z
61026.0000000000
61026.0000000000
Count the values that match a regular expression
Return the number of non-null field values for every field key that contains the
word water in the h2o_feet measurement.
SELECTCOUNT(/water/)FROM"h2o_feet"
name: h2o_feet
time
count_water_level
1970-01-01T00:00:00Z
61026.0000000000
Count distinct values for a field
Return the number of unique field values for the level description field key
and the h2o_feet measurement.
InfluxQL supports nesting DISTINCT() in COUNT().
Returns the unique field values associated with the field key.
Examples
List the distinct field values associated with a field key
Return a tabular list of the unique field values in the level description
field key in the h2o_feet measurement.
SELECTDISTINCT("level description")FROM"h2o_feet"
name: h2o_feet
time
distinct
1970-01-01T00:00:00Z
between 6 and 9 feet
1970-01-01T00:00:00Z
below 3 feet
1970-01-01T00:00:00Z
between 3 and 6 feet
1970-01-01T00:00:00Z
at or greater than 9 feet
List the distinct field values associated with each field key in a measurement
Return a tabular list of the unique field values for each field key in the h2o_feet measurement.
The h2o_feet measurement has two field keys: level description and water_level.
SELECTDISTINCT(*)FROM"h2o_feet"
name: h2o_feet
time
distinct_level description
distinct_water_level
1970-01-01T00:00:00Z
between 6 and 9 feet
8.12
1970-01-01T00:00:00Z
between 3 and 6 feet
8.005
1970-01-01T00:00:00Z
at or greater than 9 feet
7.887
1970-01-01T00:00:00Z
below 3 feet
7.762
-->
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.
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.
Calculate the integral for the field values associated with each field key in a measurement and specify the unit option
Return the area under the curve (in minutes) for the field values associated
with each field key that stores numeric values in the h2o_feet measurement.
The h2o_feet measurement has on numeric field: water_level.
Calculate the integral for the field values associated with each field key that matches a regular expression and specify the unit option
Return the area under the curve (in minutes) for the field values associated
with each field key that stores numeric values includes the word water in
the h2o_feet measurement.
Calculate the integral for the field values associated with a field key and include several clauses
Return the area under the curve (in minutes) for the field values associated
with the water_level field key and in the h2o_feet measurement in the
time range between
2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z, grouped results into 12-minute intervals, and
limit
the number of results returned to one.
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
Return the average field value in the water_level field key in the h2o_feet measurement.
SELECTMEAN("water_level")FROM"h2o_feet"
name: h2o_feet
time
mean
1970-01-01T00:00:00Z
4.4418674882
Calculate the mean field value associated with each field key in a measurement
Return the average field value for every field key that stores numeric values
in the h2o_feet measurement.
The h2o_feet measurement has one numeric field: water_level.
SELECTMEAN(*)FROM"h2o_feet"
name: h2o_feet
time
mean_water_level
1970-01-01T00:00:00Z
4.4418674882
Calculate the mean field value associated with each field key that matches a regular expression
Return the average field value for each field key that stores numeric values and
includes the word water in the h2o_feet measurement.
SELECTMEAN(/water/)FROM"h2o_feet"
name: h2o_feet
time
mean_water_level
1970-01-01T00:00:00Z
4.4418674882
Calculate the mean field value associated with a field key and include several clauses
Return the average of the values in the water_level field key in the
time range
between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z and
group
results into 12-minute time intervals and per tag.
Then fill
empty time intervals with 9.01 and
limit
the number of points and series returned to seven and one.
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.
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
Return the middle field value in the water_level field key and in the h2o_feet measurement.
SELECTMEDIAN("water_level")FROM"h2o_feet"
name: h2o_feet
time
median
1970-01-01T00:00:00Z
4.1240000000
Calculate the median field value associated with each field key in a measurement
Return the middle field value for every field key that stores numeric values in the h2o_feet measurement.
The h2o_feet measurement has one numeric field: water_level.
SELECTMEDIAN(*)FROM"h2o_feet"
name: h2o_feet
time
median_water_level
1970-01-01T00:00:00Z
4.1240000000
Calculate the median field value associated with each field key that matches a regular expression
Return the middle field value for every field key that stores numeric values and
includes the word water in the h2o_feet measurement.
SELECTMEDIAN(/water/)FROM"h2o_feet"
name: h2o_feet
time
median_water_level
1970-01-01T00:00:00Z
4.1240000000
Calculate the median field value associated with a field key and include several clauses
Return the middle field value in the water_level field key in the
time range
between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z and
group
results into 12-minute time intervals and per tag.
Then fill
empty time intervals with 700 , limit
the number of points and series returned to seven and one, and offset the series returned by one.
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
Return the most frequent field value in the level description field key and in
the h2o_feet measurement.
SELECTMODE("level description")FROM"h2o_feet"
name: h2o_feet
time
mode
1970-01-01T00:00:00Z
between 3 and 6 feet
Calculate the mode field value associated with each field key in a measurement
Return the most frequent field value for every field key in the h2o_feet measurement.
The h2o_feet measurement has two field keys: level description and water_level.
SELECTMODE(*)FROM"h2o_feet"
name: h2o_feet
time
mode_level description
mode_water_level
1970-01-01T00:00:00Z
between 3 and 6 feet
2.6900000000
Calculate the mode field value associated with each field key that matches a regular expression
Return the most frequent field value for every field key that includes the word
/water/ in the h2o_feet measurement.
SELECTMODE(/water/)FROM"h2o_feet"
name: h2o_feet
time
mode_water_level
1970-01-01T00:00:00Z
2.6900000000
Calculate the mode field value associated with a field key and include several clauses
Return the mode of the values associated with the water_level field key in the
time range
between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z and
group
results into 12-minute time intervals and per tag.
Then limits
the number of points and series returned to three and one, and it [offsets](/influxdb/v2/query-data/influxql/explore-data
#the-offset-and-soffset-clauses) the series returned by one.
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
Return the difference between the minimum and maximum field values in the
water_level field key and in the h2o_feet measurement.
SELECTSPREAD("water_level")FROM"h2o_feet"
name: h2o_feet
time
spread
1970-01-01T00:00:00Z
10.5740000000
Calculate the spread for the field values associated with each field key in a measurement
Return the difference between the minimum and maximum field values for every
field key that stores numeric values in the h2o_feet measurement.
The h2o_feet measurement has one numeric field: water_level.
SELECTSPREAD(*)FROM"h2o_feet"
name: h2o_feet
time
spread_water_level
1970-01-01T00:00:00Z
10.5740000000
Calculate the spread for the field values associated with each field key that matches a regular expression
Return the difference between the minimum and maximum field values for every
field key that stores numeric values and includes the word water in the h2o_feet measurement.
SELECTSPREAD(/water/)FROM"h2o_feet"
name: h2o_feet
time
spread_water_level
1970-01-01T00:00:00Z
10.5740000000
Calculate the spread for the field values associated with a field key and include several clauses
Return the difference between the minimum and maximum field values in the water_level field key in the
time range
between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z and
group
results into 12-minute time intervals and per tag.
Then fill
empty time intervals with 18, lim
ts
the number of points and series returned to three and one, and offsets the series returned by one.
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
Return the standard deviation of the field values in the water_level field key
and in the h2o_feet measurement.
SELECTSTDDEV("water_level")FROM"h2o_feet"
name: h2o_feet
time
stddev
1970-01-01T00:00:00Z
2.2789744110
Calculate the standard deviation for the field values associated with each field key in a measurement
Return the standard deviation of numeric fields in the h2o_feet measurement.
The h2o_feet measurement has one numeric field: water_level.
SELECTSTDDEV(*)FROM"h2o_feet"
name: h2o_feet
time
stddev_water_level
1970-01-01T00:00:00Z
2.2789744110
Calculate the standard deviation for the field values associated with each field key that matches a regular expression
Return the standard deviation of numeric fields with water in the field key in the h2o_feet measurement.
SELECTSTDDEV(/water/)FROM"h2o_feet"
name: h2o_feet
time
stddev_water_level
1970-01-01T00:00:00Z
2.2789744110
Calculate the standard deviation for the field values associated with a field key and include several clauses
Return the standard deviation of the field values in the water_level field key in the
time range
between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z and
group
results into 12-minute time intervals and per tag.
Then fill
empty time intervals with 18000, limit
the number of points and series returned to two and one, and offsets the series returned by one.
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
Return the summed total of the field values in the water_level field key and
in the h2o_feet measurement.
SELECTSUM("water_level")FROM"h2o_feet"
name: h2o_feet
time
sum
1970-01-01T00:00:00Z
271069.4053333958
Calculate the sum of the field values associated with each field key in a measurement
Return the summed total of numeric fields in the h2o_feet measurement.
The h2o_feet measurement has one numeric field: water_level.
SELECTSUM(*)FROM"h2o_feet"
name: h2o_feet
time
sum_water_level
1970-01-01T00:00:00Z
271069.4053333958
Calculate the sum of the field values associated with each field key that matches a regular expression
Return the summed total of numeric fields with water in the field key in the h2o_feet measurement.
SELECTSUM(/water/)FROM"h2o_feet"
name: h2o_feet
time
sum_water_level
1970-01-01T00:00:00Z
271069.4053333958
Calculate the sum of the field values associated with a field key and include several clauses
Return the summed total of the field values in the water_level field key in the
time range
between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z and
group
results into 12-minute time intervals and per tag.
Then fill
empty time intervals with 18000, and limit
the number of points and series returned to four and one.
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 v3 enhancements and InfluxDB Clustered is now generally available
New capabilities, including faster query performance and management
tooling advance the InfluxDB v3 product line.
InfluxDB Clustered is now generally available.
InfluxDB v3 performance and features
The InfluxDB v3 product line has seen significant enhancements in query
performance and has made new management tooling available. These enhancements
include an operational dashboard to monitor the health of your InfluxDB cluster,
single sign-on (SSO) support in InfluxDB Cloud Dedicated, and new management
APIs for tokens and databases.
