Aggregates

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

{{< expand-wrapper >}} {{% expand "Count the number of non-null values in a field" %}}

SELECT COUNT(temp) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Count the number of non-null values in each field" %}}

SELECT COUNT(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Count the number of non-null values in fields where the field key matches a regular expression" %}}

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

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Count distinct values for a field" %}}

InfluxQL supports nesting DISTINCT() in COUNT().

SELECT COUNT(DISTINCT(co)) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Count the number of non-null field values within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

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)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

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

{{< expand-wrapper >}} {{% expand "List the distinct field values" %}}

SELECT DISTINCT(co) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}} {{< /expand-wrapper >}}

INTEGRAL()

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

[!Important]

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

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

{{< expand-wrapper >}} {{% expand "Calculate the integral for a field" %}}

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

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the integral for a field and specify the unit option" %}}

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

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the integral for each field 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.

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

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the integral for the field keys that matches a regular expression" %}}

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

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the integral for a field grouping by time" %}}

{{% influxdb/custom-timestamps %}}

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)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

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

{{< expand-wrapper >}} {{% expand "Calculate the mean value of a field" %}}

SELECT MEAN(temp) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the mean value of each field" %}}

SELECT MEAN(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the mean value of fields where the field key matches a regular expression" %}}

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

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the mean value of a field within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

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)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

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

{{< expand-wrapper >}} {{% expand "Calculate the median value of a field" %}}

SELECT MEDIAN(temp) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the median value of each field" %}}

SELECT MEDIAN(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the median value of fields where the field key matches a regular expression" %}}

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

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the median value of a field within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

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)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "Calculate the mode value of a field" %}}

SELECT MODE(co) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the mode value of each field" %}}

SELECT MODE(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the mode of field keys that match a regular expression" %}}

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

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the mode a field within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

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)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "Calculate the spread of a field" %}}

SELECT SPREAD(temp) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the spread of each field" %}}

SELECT SPREAD(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the spread of field keys that match a regular expression" %}}

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

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the spread of a field within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

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)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

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

{{< expand-wrapper >}} {{% expand "Calculate the standard deviation of a field" %}}

SELECT STDDEV(temp) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the standard deviation of each field" %}}

SELECT STDDEV(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the standard deviation of fields where the field key matches a regular expression" %}}

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

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the standard deviation of a field within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

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)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}

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

{{< expand-wrapper >}} {{% expand "Calculate the sum of values in a field" %}}

SELECT SUM(co) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the sum of values in each field" %}}

SELECT SUM(*) FROM home

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the sum of values for fields where the field key matches a regular expression" %}}

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

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the sum of values in a field within time windows (grouped by time)" %}}

{{% influxdb/custom-timestamps %}}

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)

{{% influxql/table-meta %}} name: home {{% /influxql/table-meta %}}

{{% /influxdb/custom-timestamps %}}

{{% /expand %}} {{< /expand-wrapper >}}