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.
Examples use the sample data set provided in the
Get started with InfluxDB tutorial.
Missing InfluxQL functions
Some InfluxQL functions are in the process of being rearchitected to work with
the InfluxDB 3.0 storage engine. If a function you need is not here, check the
InfluxQL feature support page
for more information.
COUNT()
Returns the number of non-null field values.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports all field data types.
Examples
Count the number of non-null values in a field
SELECT COUNT(temp) FROM home
time | count |
---|
1970-01-01T00:00:00Z | 26 |
Count the number of non-null values in each field
SELECT COUNT(*) FROM home
time | count_co | count_hum | count_temp |
---|
1970-01-01T00:00:00Z | 26 | 26 | 26 |
Count the number of non-null values in fields where the field key matches a regular expression
SELECT COUNT(/^[th]/) FROM home
time | count_hum | count_temp |
---|
1970-01-01T00:00:00Z | 26 | 26 |
Count distinct values for a field
InfluxQL supports nesting DISTINCT()
in COUNT()
.
SELECT COUNT(DISTINCT(co)) FROM home
time | count |
---|
1970-01-01T00:00:00Z | 12 |
Count the number of non-null field values within time windows (grouped by time)
SELECT
COUNT(temp)
FROM home
WHERE
room = 'Kitchen'
AND time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)
time | count |
---|
2022-01-01T06:00:00Z | 4 |
2022-01-01T12:00:00Z | 6 |
2022-01-01T18:00:00Z | 3 |
DISTINCT()
Returns the list of unique field values.
Arguments
- field_key: Field key to return distinct values from.
Supports all field types.
Notable behaviors
- InfluxQL supports nesting
DISTINCT()
with COUNT()
.
Examples
List the distinct field values
SELECT DISTINCT(co) FROM home
time | distinct |
---|
1970-01-01T00:00:00Z | 0 |
1970-01-01T00:00:00Z | 1 |
1970-01-01T00:00:00Z | 3 |
1970-01-01T00:00:00Z | 4 |
1970-01-01T00:00:00Z | 7 |
1970-01-01T00:00:00Z | 5 |
1970-01-01T00:00:00Z | 9 |
1970-01-01T00:00:00Z | 18 |
1970-01-01T00:00:00Z | 14 |
1970-01-01T00:00:00Z | 22 |
1970-01-01T00:00:00Z | 17 |
1970-01-01T00:00:00Z | 26 |
MEAN()
Returns the arithmetic mean (average) of field values.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric fields.
Examples
Calculate the mean value of a field
SELECT MEAN(temp) FROM home
time | mean |
---|
1970-01-01T00:00:00Z | 22.396153846153844 |
Calculate the mean value of each field
time | mean_co | mean_hum | mean_temp |
---|
1970-01-01T00:00:00Z | 5.269230769230769 | 36.15 | 22.396153846153844 |
Calculate the mean value of fields where the field key matches a regular expression
SELECT MEAN(/^[th]/) FROM home
time | mean_hum | mean_temp |
---|
1970-01-01T00:00:00Z | 36.15 | 22.396153846153844 |
Calculate the mean value of a field within time windows (grouped by time)
SELECT
MEAN(temp)
FROM home
WHERE
room = 'Kitchen'
AND time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)
time | mean |
---|
2022-01-01T06:00:00Z | 22.275 |
2022-01-01T12:00:00Z | 22.649999999999995 |
2022-01-01T18:00:00Z | 23.033333333333335 |
Returns the middle value from a sorted list of field values.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric fields.
Notable behaviors
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.
Examples
MODE()
Returns the most frequent value in a list of
field values.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports all field types.
Notable behaviors
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.
Examples
Calculate the mode value of a field
SELECT MODE(co) FROM home
time | mode |
---|
1970-01-01T00:00:00Z | 0 |
Calculate the mode value of each field
time | mode_co | mode_hum | mode_temp |
---|
1970-01-01T00:00:00Z | 0 | 36 | 22.7 |
Calculate the mode of field keys that match a regular expression
SELECT MODE(/^[th]/) FROM home
time | mode_hum | mode_temp |
---|
1970-01-01T00:00:00Z | 36 | 22.7 |
Calculate the mode a field within time windows (grouped by time)
SELECT
MODE(co)
FROM home
WHERE
room = 'Kitchen'
AND time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)
time | mode |
---|
2022-01-01T06:00:00Z | 0 |
2022-01-01T12:00:00Z | 1 |
2022-01-01T18:00:00Z | 18 |
SPREAD()
Returns the difference between the minimum and maximum
field values.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric fields.
Examples
Calculate the spread of a field
SELECT SPREAD(temp) FROM home
time | spread |
---|
1970-01-01T00:00:00Z | 2.3000000000000007 |
Calculate the spread of each field
SELECT SPREAD(*) FROM home
time | spread_co | spread_hum | spread_temp |
---|
1970-01-01T00:00:00Z | 26 | 1 | 2.3000000000000007 |
Calculate the spread of field keys that match a regular expression
SELECT SPREAD(/^[th]/) FROM home
time | spread_hum | spread_temp |
---|
1970-01-01T00:00:00Z | 1 | 2.3000000000000007 |
Calculate the spread of a field within time windows (grouped by time)
SELECT
SPREAD(co)
FROM home
WHERE
room = 'Kitchen'
AND time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)
time | spread |
---|
2022-01-01T06:00:00Z | 0 |
2022-01-01T12:00:00Z | 9 |
2022-01-01T18:00:00Z | 8 |
STDDEV()
Returns the standard deviation of field values.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric fields.
Examples
Calculate the standard deviation of a field
SELECT STDDEV(temp) FROM home
time | stddev |
---|
1970-01-01T00:00:00Z | 0.5553238833191091 |
Calculate the standard deviation of each field
SELECT STDDEV(*) FROM home
time | stddev_co | stddev_hum | stddev_temp |
---|
1970-01-01T00:00:00Z | 7.774613519951676 | 0.25495097567963926 | 0.5553238833191091 |
Calculate the standard deviation of fields where the field key matches a regular expression
SELECT STDDEV(/^[th]/) FROM home
time | stddev_hum | stddev_temp |
---|
1970-01-01T00:00:00Z | 0.25495097567963926 | 0.5553238833191091 |
Calculate the standard deviation of a field within time windows (grouped by time)
SELECT
STDDEV(co)
FROM home
WHERE
room = 'Kitchen'
AND time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)
time | stddev |
---|
2022-01-01T06:00:00Z | 0 |
2022-01-01T12:00:00Z | 3.6742346141747673 |
2022-01-01T18:00:00Z | 4 |
SUM()
Returns the sum of field values.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric fields.
Examples
Calculate the sum of values in a field
time | sum |
---|
1970-01-01T00:00:00Z | 137 |
Calculate the sum of values in each field
time | sum_co | sum_hum | sum_temp |
---|
1970-01-01T00:00:00Z | 137 | 939.9 | 582.3 |
Calculate the sum of values for fields where the field key matches a regular expression
SELECT SUM(/^[th]/) FROM home
time | sum_hum | sum_temp |
---|
1970-01-01T00:00:00Z | 939.9 | 582.3 |
Calculate the sum of values in a field within time windows (grouped by time)
SELECT
SUM(co)
FROM home
WHERE
room = 'Kitchen'
AND time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)
time | sum |
---|
2022-01-01T06:00:00Z | 0 |
2022-01-01T12:00:00Z | 21 |
2022-01-01T18:00:00Z | 66 |
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:
Customers with an annual or support contract can contact InfluxData Support.