---
title: Subqueries
description: Use a subquery to apply a query as a condition in the enclosing query.
url: https://docs.influxdata.com/influxdb/v2/query-data/influxql/explore-data/subqueries/
estimated_tokens: 2830
product: InfluxDB OSS v2
version: v2
publisher: InfluxData
canonical: https://docs.influxdata.com/influxdb/v2/query-data/influxql/explore-data/subqueries/
date: '2025-04-02T15:54:32-06:00'
lastmod: '2025-04-02T15:54:32-06:00'
---

This page documents an earlier version of InfluxDB OSS.[InfluxDB 3 Core](/influxdb3/core/) is the latest stable version.

#### API token hashing is enabled by default in InfluxDB OSS 2.9.0

Stronger token security: tokens are stored as hashes on disk, so a
copy of the database file doesn’t expose usable tokens. Existing
tokens are hashed on first startup and the original strings can’t
be recovered afterward — **capture any plaintext tokens you still
need before you upgrade**.

For more information, see [Token hashing](/influxdb/v2/admin/tokens/#token-hashing).

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](https://en.wikipedia.org/wiki/Having_%28SQL%29).

> [!Note]
> **Note:** InfluxQL does not support a `HAVING` clause.

* [Syntax](#syntax)
* [Examples](#examples)
* [Common Issues](#common-issues-with-subqueries)

### Syntax

```sql
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](/influxdb/v2/query-data/influxql/explore-data/select/) and the [`FROM` clause](/influxdb/v2/query-data/influxql/explore-data/select/#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:

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

> [!Note]
> **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'
> )
> ```

##### Time bounds on the outer query (recommended)

```sql
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

```sql
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)

Calculate the SUM() of several MAX() values

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

Output:

Name: h2o\_feet

|        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`:

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

Output:

name: h2o\_feet  
tags: location=coyote\_creek

|        time        |    max     |
|--------------------|------------|
|2015-08-29T07:24:00Z|9.9640000000|

name: h2o\_feet  
tags: location=santa\_monica

|        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)

Calculate the MEAN() difference between two fields

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

Output:

Name: pet\_daycare

|        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`:

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

Output:

Name: pet\_daycare

|        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()`](/influxdb/v2/query-data/influxql/functions/aggregates/#mean) function.

[](#calculate-several-mean-values-and-place-a-condition-on-those-mean-values)

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

```sql
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

|        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`:

```sql
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

|        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)

Calculate the SUM() of several DERIVATIVE() values

```sql
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

|        time        |sum\_derivative|
|--------------------|---------------|
|1970-01-01T00:00:00Z|\-0.5315000000 |

name: h2o\_feet  
tags: location=santa\_monica

|        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`:

```sql
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

|        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     |

name: h2o\_feet  
tags: location=santa\_monica

|        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()`](/influxdb/v2/query-data/influxql/functions/aggregates/#sum) function.

### Common issues with subqueries

#### Multiple statements in a subquery

InfluxQL supports multiple nested subqueries per main query:

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

InfluxQL does not support multiple [`SELECT` statements](/influxdb/v2/query-data/influxql/explore-data/select/) per subquery:

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

The system returns a parsing error if a subquery includes multiple `SELECT` statements.
| time | sum |
| --- | --- |
| time | sum |
| 1970-01-01T00:00:00Z | 17.169 |

| time | max |
| --- | --- |
| time | max |
| 2015-08-29T07:24:00Z | 9.9640000000 |

| time | max |
| --- | --- |
| time | max |
| 2015-08-29T03:54:00Z | 7.2050000000 |

| time | max |
| --- | --- |
| time | max |
| 1970-01-01T00:00:00Z | 1.75 |

| time | difference |
| --- | --- |
| 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 |

| time | all_the_means |
| --- | --- |
| 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 |

| time | all_the_means |
| --- | --- |
| 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 |

| time | sum_derivative |
| --- | --- |
| time | sum_derivative |
| 1970-01-01T00:00:00Z | -0.5315000000 |

| time | sum_derivative |
| --- | --- |
| time | sum_derivative |
| 1970-01-01T00:00:00Z | -0.2375000000 |

| time | water_level_derivative |
| --- | --- |
| 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 |
| --- | --- |
| 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 |