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.

• COUNT()
• DISTINCT()
• INTEGRAL()
• MEAN()
• MEDIAN()
• MODE()
• SPREAD()
• STDDEV()
• SUM()

COUNT()

Returns the number of non-null field values.

COUNT(field_expression)

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.

Examples

SELECT COUNT(temp) FROM home

SELECT COUNT(*) FROM home

SELECT COUNT(/^[th]/) FROM home

SELECT COUNT(DISTINCT(co)) FROM home

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)

DISTINCT()

Returns the list of unique field values.

DISTINCT(field_key)

Arguments

• field_key: Field key to return distinct values from. Supports all field types.

Notable behaviors

• InfluxQL supports nesting DISTINCT() with COUNT().

Examples

SELECT DISTINCT(co) FROM home

INTEGRAL()

Returns the area under the curve for queried field values and converts those results into the summed area per unit of time.

INTEGRAL(field_expression[, unit])

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*).
• unit: Unit of time to use when calculating the integral. Default is 1s (one second).

Examples

SELECT
  INTEGRAL(co)
FROM home
WHERE room = 'Kitchen'

SELECT
  INTEGRAL(co, 1h)
FROM home
WHERE room = 'Kitchen'

SELECT
  INTEGRAL(*, 1h)
FROM home
WHERE room = 'Kitchen'

SELECT
  INTEGRAL(/^[th]/, 1h)
FROM home
WHERE room = 'Kitchen'

SELECT
  INTEGRAL(co, 1h)
FROM home
WHERE
  room = 'Kitchen'
  AND time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)

MEAN()

Returns the arithmetic mean (average) of field values.

MEAN(field_expression)

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

SELECT MEAN(temp) FROM home

SELECT MEAN(*) FROM home

SELECT MEAN(/^[th]/) FROM home

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)

MEDIAN()

Returns the middle value from a sorted list of field values.

MEDIAN(field_expression)

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

SELECT MEDIAN(temp) FROM home

SELECT MEDIAN(*) FROM home

SELECT MEDIAN(/^[th]/) FROM home

SELECT
  MEDIAN(temp)
FROM home
WHERE
  room = 'Kitchen'
  AND time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)

MODE()

Returns the most frequent value in a list of field values.

MODE(field_expression)

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

SELECT MODE(co) FROM home

SELECT MODE(*) FROM home

SELECT MODE(/^[th]/) FROM home

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)

SPREAD()

Returns the difference between the minimum and maximum field values.

SPREAD(field_expression)

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

SELECT SPREAD(temp) FROM home

SELECT SPREAD(*) FROM home

SELECT SPREAD(/^[th]/) FROM home

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)

STDDEV()

Returns the standard deviation of field values.

STDDEV(field_expression)

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

SELECT STDDEV(temp) FROM home

SELECT STDDEV(*) FROM home

SELECT STDDEV(/^[th]/) FROM home

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)

SUM()

Returns the sum of field values.

SUM(field_expression)

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

SELECT SUM(co) FROM home

SELECT SUM(*) FROM home

SELECT SUM(/^[th]/) FROM home

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)