Subqueries

A subquery is a query that is nested in the FROM clause of another query. Use a subquery to apply a query as a condition in the enclosing query. Subqueries offer functionality similar to nested functions and the SQL HAVING clause.

Note: InfluxQL does not support a HAVING clause.

• Syntax
• Examples
• Common Issues

Syntax

SELECT_clause FROM ( SELECT_statement ) [...]

InfluxDB performs the subquery first and the main query second.

The main query surrounds the subquery and requires at least the SELECT clause and the FROM clause. The main query supports all clauses listed in InfluxQL 2.x documentation.

The subquery appears in the main query’s FROM clause, and it requires surrounding parentheses. The subquery also supports all clauses listed in InfluxQL 2.x documentation.

InfluxQL supports multiple nested subqueries per main query. Sample syntax for multiple subqueries:

SELECT_clause FROM ( SELECT_clause FROM ( SELECT_statement ) [...] ) [...]

Note: #### Improve performance of time-bound subqueries To improve the performance of InfluxQL queries with time-bound subqueries, apply the WHERE time clause to the outer query instead of the inner query. For example, the following queries return the same results, but the query with time bounds on the outer query is more performant than the query with time bounds on the inner query:

Time bounds on the outer query (recommended)

SELECT inner_value AS value FROM (SELECT raw_value as inner_value)
WHERE time >= '2022-07-19T21:00:00Z'
AND time <= '2022-07-20T22:00:00Z'

Time bounds on the inner query

SELECT inner_value AS value FROM (
  SELECT raw_value as inner_value
  WHERE time >= '2022-07-19T21:00:00Z'
  AND time <= '2022-07-20T22:00:00Z'
)

Examples

Calculate the SUM() of several MAX() values

SELECT SUM("max") FROM (SELECT MAX("water_level") FROM "h2o_feet" GROUP BY "location")

Output:

Name: h2o_feet

The query returns the sum of the maximum water_level values across every tag value of location.

InfluxDB first performs the subquery; it calculates the maximum value of water_level for each tag value of location:

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

Output:

name: h2o_feet
tags: location=coyote_creek

name: h2o_feet
tags: location=santa_monica

Next, InfluxDB performs the main query and calculates the sum of those maximum values: 9.9640000000 + 7.2050000000 = 17.169. Notice that the main query specifies max, not water_level, as the field key in the SUM() function.

Calculate the MEAN() difference between two fields

SELECT MEAN("difference") FROM (SELECT "cats" - "dogs" AS "difference" FROM "pet_daycare")

Output:

Name: pet_daycare

The query returns the average of the differences between the number of cats and dogs in the pet_daycare measurement.

InfluxDB first performs the subquery. The subquery calculates the difference between the values in the cats field and the values in the dogs field, and it names the output column difference:

SELECT "cats" - "dogs" AS "difference" FROM "pet_daycare"

Output:

Name: pet_daycare

Next, InfluxDB performs the main query and calculates the average of those differences. Notice that the main query specifies difference as the field key in the MEAN() function.

Calculate several MEAN() values and place a condition on those mean values

SELECT "all_the_means" FROM (SELECT MEAN("water_level") AS "all_the_means" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' GROUP BY time(12m) ) WHERE "all_the_means" > 5

Output:

Name: h2o_feet

The query returns all mean values of the water_level field that are greater than five.

InfluxDB first performs the subquery. The subquery calculates MEAN() values of water_level from 2019-08-18T00:00:00Z through 2019-08-18T00:30:00Z and groups the results into 12-minute intervals. It also names the output column all_the_means:

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

Output:

Name: h2o_feet

Next, InfluxDB performs the main query and returns only those mean values that are greater than five. Notice that the main query specifies all_the_means as the field key in the SELECT clause.

Calculate the SUM() of several DERIVATIVE() values

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

Output:

name: h2o_feet
tags: location=coyote_creek

name: h2o_feet
tags: location=santa_monica

The query returns the sum of the derivative of average water_level values for each tag value of location.

InfluxDB first performs the subquery. The subquery calculates the derivative of average water_level values taken at 12-minute intervals. It performs that calculation for each tag value of location and names the output column water_level_derivative:

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

Output:

name: h2o_feet
tags: location=coyote_creek

name: h2o_feet
tags: location=santa_monica

Next, InfluxDB performs the main query and calculates the sum of the water_level_derivative values for each tag value of location. Notice that the main query specifies water_level_derivative, not water_level or derivative, as the field key in the SUM() function.

Common issues with subqueries

Multiple statements in a subquery

InfluxQL supports multiple nested subqueries per main query:

SELECT_clause FROM ( SELECT_clause FROM ( SELECT_statement ) [...] ) [...]
                     ------------------   ----------------
                         Subquery 1          Subquery 2

InfluxQL does not support multiple SELECT statements per subquery:

SELECT_clause FROM (SELECT_statement; SELECT_statement) [...]

The system returns a parsing error if a subquery includes multiple SELECT statements.