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
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:
| time | sum |
|---|
| 1970-01-01T00:00:00Z | 17.169 |
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:
| time | max |
|---|
| 2015-08-29T07:24:00Z | 9.9640000000 |
| time | max |
|---|
| 2015-08-29T03:54:00Z | 7.2050000000 |
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:
| time | max |
|---|
| 1970-01-01T00:00:00Z | 1.75 |
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:
| time | difference |
|---|
| 2017-01-20T00:55:56Z | -1 |
| 2017-01-21T00:55:56Z | -49 |
| 2017-01-22T00:55:56Z | 66 |
| 2017-01-23T00:55:56Z | -9 |
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:
| time | all_the_means |
|---|
| 2019-08-18T00:00:00Z | 5.4135000000 |
| 2019-08-18T00:12:00Z | 5.3042500000 |
| 2019-08-18T00:24:00Z | 5.1682500000 |
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:
| time | all_the_means |
|---|
| 2019-08-18T00:00:00Z | 5.4135000000 |
| 2019-08-18T00:12:00Z | 5.3042500000 |
| 2019-08-18T00:24:00Z | 5.1682500000 |
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:
| time | sum_derivative |
|---|
| 1970-01-01T00:00:00Z | -0.5315000000 |
| time | sum_derivative |
|---|
| 1970-01-01T00:00:00Z | -0.2375000000 |
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:
| time | water_level_derivative |
|---|
| 2019-08-18T00:00:00Z | -0.1410000000 |
| 2019-08-18T00:12:00Z | -0.1890000000 |
| 2019-08-18T00:24:00Z | -0.2015000000 |
| time | water_level_derivative |
|---|
| 2019-08-18T00:00:00Z | -0.1375000000 |
| 2019-08-18T00:12:00Z | -0.0295000000 |
| 2019-08-18T00:24:00Z | -0.0705000000 |
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.
Was this page helpful?
Thank you for your feedback!
Support and feedback
Thank you for being part of our community!
We welcome and encourage your feedback and bug reports for InfluxDB and this documentation.
To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.
