GROUP BY clause

Use the GROUP BY clause to group data by one or more specified tags or into specified time intervals. GROUP BY requires an aggregate or selector function in the SELECT statement.

• Syntax
• GROUP BY clause behaviors
• GROUP BY tags
  • GROUP BY tags examples
• GROUP BY time
  • GROUP by time and fill gaps
  • GROUP BY time examples - GROUP BY time with offset - GROUP BY time and fill gaps
• Result set
  • Default time range
• Notable behaviors of the GROUP BY clause
  • Cannot group by fields
  • Tag order does not matter
  • Grouping by tag and no time range returns unexpected timestamps
  • Data grouped by time may return unexpected timestamps
    • Example data
    • Query results
  • Fill with no data in the queried time range
  • Fill with previous if no previous value exists
  • Fill with linear interpolation if there are not two values to interpolate between

Syntax

SELECT_clause FROM_clause [WHERE_clause] GROUP BY group_expression[, ..., group_expression_n]

• group_expression: Expression to identify tags or time intervals to group by. Can be a tag key, constant, regular expression, wildcard (*), or function expression.

GROUP BY clause behaviors

• GROUP BY tag_key - Groups data by a specific tag
• GROUP BY tag_key1, tag_key2 - Groups data by more than one tag
• GROUP BY * - Groups data by all tags
• GROUP BY /regex/ - Groups data by tag keys that match the regular expression
• GROUP BY time() - Groups data into time intervals (windows)

GROUP BY tags

Groups data by one or more tag columns.

GROUP BY tags examples

The following examples use the Bitcoin price sample data.

SELECT MEAN(*) FROM bitcoin GROUP BY code

SELECT MEAN(*) FROM bitcoin GROUP BY code, description

SELECT MEAN(*) FROM bitcoin GROUP BY *

SELECT MEAN(*) FROM bitcoin GROUP BY /^[cd]/

GROUP BY time

GROUP BY time() groups data by into specified time intervals, also known as “windows”, and applies the aggregate and selector functions in the SELECT clause to each interval. Use the time() function to specify the time interval to group by.

SELECT_clause FROM_clause WHERE <time_range> GROUP BY time(time_interval[, offset])[, group_expression (...)] [fill(behavior)]

GROUP BY time() intervals use preset round-number time boundaries that are independent of time conditions in the WHERE clause. Output data uses window start boundaries as the aggregate timestamps. Use the offset argument of the time() function to shift time boundaries forward or backward in time.

GROUP by time and fill gaps

When grouping by time, if a window in the queried time range does not contain data, results return a row for the empty window containing the timestamp of the empty window and null values for each queried field. Use the fill() function at the end of the GROUP BY clause to replace null field values. If no FILL clause is included, the default behavior is fill(null).

fill() provides the following behaviors for filling values:

• numeric literal: Replaces null values with the specified numeric literal.
• linear: Uses linear interpolation between existing values to replace null values.
• none: Removes rows with null field values.
• null: Keeps null values and associated timestamps.
• previous: Replaces null values with the most recent non-null value.

See the fill() documentation for detailed examples.

GROUP BY time examples

The following examples use the Bitcoin price sample data.

SELECT
  MEAN(price)
FROM bitcoin
WHERE
  code = 'USD'
  AND time >= '2023-05-01T00:00:00Z'
  AND time < '2023-05-15T00:00:00Z'
GROUP BY time(1h)

SELECT
  MEAN(price)
FROM bitcoin
WHERE
  time >= '2023-05-01T00:00:00Z'
  AND time < '2023-05-15T00:00:00Z'
GROUP BY time(1w), code

GROUP BY time with offset

SELECT
  MEAN(price)
FROM bitcoin
WHERE
  code = 'USD'
  AND time >= '2023-05-01T00:00:00Z'
  AND time < '2023-05-02T00:00:00Z'
GROUP BY time(1h, 15m)

SELECT
  MEAN(price)
FROM bitcoin
WHERE
  code = 'USD'
  AND time >= '2023-05-01T00:00:00Z'
  AND time < '2023-05-02T00:00:00Z'
GROUP BY time(1h, -15m)

GROUP BY time and fill gaps

SELECT
  MEAN(price)
FROM bitcoin
WHERE
  code = 'USD'
  AND time >= '2023-05-01T00:00:00Z'
  AND time < '2023-05-01T02:00:00Z'
GROUP BY
  time(30m)
  fill(0)

SELECT
  MEAN(price)
FROM bitcoin
WHERE
  code = 'USD'
  AND time >= '2023-05-01T00:00:00Z'
  AND time < '2023-05-01T02:00:00Z'
GROUP BY
  time(30m)
  fill(linear)

SELECT
  MEAN(price)
FROM bitcoin
WHERE
  code = 'USD'
  AND time >= '2023-05-01T00:00:00Z'
  AND time < '2023-05-01T02:00:00Z'
GROUP BY
  time(30m)
  fill(previous)

Result set

If at least one row satisfies the query, InfluxDB Clustered returns row data in the query result set. If a query uses a GROUP BY clause, the result set includes the following:

• Columns listed in the query’s SELECT clause
• A time column that contains the timestamp for the record or the group
• An iox::measurement column that contains the record’s measurement (table) name
• Columns listed in the query’s GROUP BY clause; each row in the result set contains the values used for grouping

Default time range

If a query doesn’t specify a time range in the WHERE clause, InfluxDB uses the default time range for filtering and grouping by time. If a query includes the GROUP BY clause and doesn’t specify a time range in the WHERE clause, the default time group is the default time range, and the time column in the result set contains the start of the range–for example:

SELECT mean(temp) FROM home GROUP BY room

Notable behaviors of the GROUP BY clause

Cannot group by fields

InfluxQL does not support grouping data by fields.

Tag order does not matter

The order that tags are listed in the GROUP BY clause does not affect how data is grouped.

Grouping by tag and no time range returns unexpected timestamps

The time column contains the start of the default time range.

Data grouped by time may return unexpected timestamps

Because GROUP BY time() intervals use preset round-number time boundaries that are independent of time conditions in the WHERE clause, results may include timestamps outside of the queried time range. Results represent only data with timestamps in the specified time range, but output timestamps are determined by by the preset time boundaries.

The following example groups data by 1-hour intervals, but the time range defined in the WHERE clause covers only part of a window:

SELECT MEAN(field)
FROM example 
WHERE
  time >= '2022-01-01T00:30:00Z'
  AND time <= '2022-01-01T01:30:00Z'
GROUP BY time(1h)

Fill with no data in the queried time range

Queries ignore fill() if no data exists in the queried time range. This is the expected behavior.

Fill with previous if no previous value exists

fill(previous) doesn’t fill null values if there is no previous value in the queried time range.

Fill with linear interpolation if there are not two values to interpolate between

fill(linear) doesn’t fill null values if there are no values before or after the null value in the queried time range.