GROUP BY clause

SELECT MEAN("water_level") FROM "h2o_feet" GROUP BY "location"

Output:

The query uses the InfluxQL MEAN() function to calculate the average water_level for each tag value of location in the h2o_feet measurement. InfluxDB returns results in two series: one for each tag value of location.

SELECT MEAN("index") FROM "h2o_quality" GROUP BY "location","randtag"

Output:

The query uses the InfluxQL MEAN() function to calculate the average index for each combination of the location tag and the randtag tag in the h2o_quality measurement. Separate multiple tags with a comma in the GROUP BY clause.

SELECT MEAN("index") FROM "h2o_quality" GROUP BY *

Output:

The query uses the InfluxQL MEAN() function to calculate the average index for every possible tag combination in the h2o_quality measurement.

SELECT MAX("water_level"),location FROM "h2o_feet" GROUP BY /l/

This query uses a regular expression to group by tags that start with l. With the sample NOAA water dataset, results are grouped by the location tag.

SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' GROUP BY time(12m)

Output:

The query uses the InfluxQL COUNT() function to count the number of water_level points per location, per 12-minute interval.

Each output row represents a single 12 minute interval. The count for the first timestamp covers the raw data between 2019-08-18T00:00:00Z and up to, but not including, 2019-08-18T00:12:00Z. The count for the second timestamp covers the raw data between 2019-08-18T00:12:00Z and up to, but not including, 2019-08-18T00:24:00Z.

SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' GROUP BY time(12m),"location"

Output:

The query uses the InfluxQL COUNT() function to count the number of water_level points per location, per 12 minute interval. Note that the time interval and the tag key are separated by a comma in the GROUP BY clause.

The query returns two series of results: one for each tag value of the location tag. The result for each timestamp represents a single 12 minute interval. Each output row represents a single 12 minute interval. and up to, but not including, 2019-08-18T00:12:00Z. The count for the second timestamp covers the raw data between 2019-08-18T00:12:00Z and up to, but not including, 2019-08-18T00:24:00Z.

SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2019-08-18T00:06:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(18m,6m)

Output:

The query uses the InfluxQL MEAN() function to calculate the average water_level, grouping results into 18 minute time intervals, and offsetting the preset time boundaries by 6 minutes.

The time boundaries and returned timestamps for the query without the offset_interval adhere to the InfluxDB database’s preset time boundaries. Let’s first examine the results without the offset:

SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2019-08-18T00:06:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(18m)

Output:

The time boundaries and returned timestamps for the query without the offset_interval adhere to the InfluxDB database’s preset time boundaries:

The first preset 18-minute time boundary begins at 00:00 and ends just before 00:18. Two raw points (8.005 and 7.887) fall both within the first GROUP BY time() interval and in that first time boundary. While the returned timestamp occurs before the start of the queried time range, the query result excludes data that occur before the queried time range.

The second preset 18-minute time boundary begins at 00:18 and ends just before 00:36. Three raw points (7.762 and 7.635 and 7.5) fall both within the second GROUP BY time() interval and in that second time boundary. In this case, the boundary time range and the interval’s time range are the same.

The fourth preset 18-minute time boundary begins at 00:54 and ends just before 1:12:00. One raw point (6.982) falls both within the fourth GROUP BY time() interval and in that fourth time boundary.

The time boundaries and returned timestamps for the query with the offset_interval adhere to the offset time boundaries:

The six-minute offset interval shifts forward the preset boundary’s time range such that the boundary time ranges and the relevant GROUP BY time() interval time ranges are always the same. With the offset, each interval performs the calculation on three points, and the timestamp returned matches both the start of the boundary time range and the start of the GROUP BY time() interval time range.

Note that offset_interval forces the fourth time boundary to be outside the queried time range so the query returns no results for that last interval.

SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2019-08-18T00:06:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(18m,-12m)

Output:

The query uses the InfluxQL MEAN() function to calculate the average water_level, grouping results into 18 minute time intervals, and offsetting the preset time boundaries by -12 minutes.

The time boundaries and returned timestamps for the query without the offset_interval adhere to InfluxDB database’s preset time boundaries. Let’s first examine the results without the offset:

SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2019-08-18T00:06:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(18m)

Output:

The time boundaries and returned timestamps for the query without the offset_interval adhere to the InfluxDB database’s preset time boundaries:

The first preset 18-minute time boundary begins at 00:00 and ends just before 00:18. Two raw points (8.005 and 7.887) fall both within the first GROUP BY time() interval and in that first time boundary. Note that while the returned timestamp occurs before the start of the queried time range, the query result excludes data that occur before the queried time range.

The second preset 18-minute time boundary begins at 00:18 and ends just before 00:36. Three raw points (7.762 and 7.635 and 7.5) fall both within the second GROUP BY time() interval and in that second time boundary. In this case, the boundary time range and the interval’s time range are the same.

The fourth preset 18-minute time boundary begins at 00:54 and ends just before 1:12:00. One raw point (6.982) falls both within the fourth GROUP BY time() interval and in that fourth time boundary.

The time boundaries and returned timestamps for the query with the offset_interval adhere to the offset time boundaries:

The negative 12-minute offset interval shifts back the preset boundary’s time range such that the boundary time ranges and the relevant GROUP BY time() interval time ranges are always the same. With the offset, each interval performs the calculation on three points, and the timestamp returned matches both the start of the boundary time range and the start of the GROUP BY time() interval time range.

Note that offset_interval forces the first time boundary to be outside the queried time range so the query returns no results for that first interval.

This example is a continuation of the scenario outlined in Common Issues with Basic Syntax.

SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2019-08-18T00:06:00Z' AND time < '2019-08-18T00:18:00Z' GROUP BY time(12m,6m)

Output:

The query uses the InfluxQL COUNT() function to count the number of water_level points per location, per 12-minute interval, and offset the preset time boundaries by six minutes.

The time boundaries and returned timestamps for the query without the offset_interval adhere to InfluxDB database’s preset time boundaries. Let’s first examine the results without the offset:

SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2019-08-18T00:06:00Z' AND time < '2019-08-18T00:18:00Z' GROUP BY time(12m)

Output:

The time boundaries and returned timestamps for the query without the offset_interval adhere to InfluxDB database’s preset time boundaries:

The first preset 12-minute time boundary begins at 00:00 and ends just before 00:12. Only one raw point (8.005) falls both within the query’s first GROUP BY time() interval and in that first time boundary. Note that while the returned timestamp occurs before the start of the queried time range, the query result excludes data that occur before the queried time range.

The second preset 12-minute time boundary begins at 00:12 and ends just before 00:24. Only one raw point (7.887) falls both within the query’s second GROUP BY time() interval and in that second time boundary.

The time boundaries and returned timestamps for the query with the offset_interval adhere to the offset time boundaries:

The six-minute offset interval shifts forward the preset boundary’s time range such that the preset boundary time range and the relevant GROUP BY time() interval time range are the same. With the offset, the query returns a single result, and the timestamp returned matches both the start of the boundary time range and the start of the GROUP BY time() interval time range.

Note that offset_interval forces the second time boundary to be outside the queried time range so the query returns no results for that second interval.