Subqueries

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

Output:

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:

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.

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

Output:

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:

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.

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:

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:

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.

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:

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:

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.