# Use data from one query to aggregate data from a second query

**URL:** <https://community.influxdata.com/t/use-data-from-one-query-to-aggregate-data-from-a-second-query/19284>\
**Category:** Fluxlang\
**Tags:** influxdb, influxql\
**Created:** [April 7, 2021, 6:48pm UTC](https://community.influxdata.com/t/use-data-from-one-query-to-aggregate-data-from-a-second-query/19284 "2021-04-07T18:48:29Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![BeSc](https://avatars.discourse-cdn.com/v4/letter/b/e19adc/32.png) [@BeSc](https://community.influxdata.com/u/BeSc)\
**Post date:** [April 7, 2021, 6:48pm UTC](https://community.influxdata.com/t/use-data-from-one-query-to-aggregate-data-from-a-second-query/19284/1 "2021-04-07T18:48:29Z")

</div>

Hi,

I have two Timeseries looking like this:

> ```
> TimeSeriesA
> _time | _timestamp
> t1 | ts1
> t2 | ts1
> t3 | ts1
> t4 | ts2
> t5 | ts2
> t6 | ts2
> ...
> 
> ```

> ```
> TimeSeriesB
> _time | _temperature
> t1 | temp1
> t2 | temp2
> t3 | temp3
> t4 | temp4
> t5 | temp5
> t6 | temp6
> ...
> 
> ```

What I’m trying to reach is following:

> TimeSeriesTarget  
> \_timestamp | \_temperature(avg)  
> ts1 | avg(temp1, temp2, temp3)  
> ts2 | avg(temp4, temp5, temp6)  
> …

Note:  
\_time: is identical in TimeSeriesA and TimeSeriesB  
\_timestamp: has a sequence of m-times identical value (above shown 3-times)

What it shall do is to:  
a) select from TimeSeriesA a “set” of \_timestamp’s  
b) walk through “set” of \_timestamp’s and select from TimeSeriesA all \_time values meeting \_timestamp  
c) use \_time values from b) to select \_temperature values from TimeSeriesB and calculate avg  
d) combine \_timestamp’s (out of a) and \_temperature values (out of c) into a new TimeSeries

I would highly appreciate any help.

Bernhard

* * *

Update 2021-04-07T22:00:00Z  
Having meanwhile Chronograf installed I’m trying to figure out how to join the two tables based on \_time. The current output shows No Result despite the fact having check the \_time column in both tables being equal.

> fcBucket = “openhab/autogen”  
> fcTimestamp = “Localweatherandforecast\_ForecastHours03\_Timestamp”  
> fcTemperature = “Localweatherandforecast\_ForecastHours03\_Temperature”
> 
> temp1 = from(bucket: fcBucket)  
> |\> range(start: dashboardTime)  
> |\> filter(fn: (r) =\> r.\_measurement == fcTimestamp and (r.\_field == “value”))  
> |\> window(every: autoInterval)  
> //|\> yield(name: “temp1”)
> 
> temp2 = from(bucket: fcBucket)  
> |\> range(start: dashboardTime)  
> |\> filter(fn: (r) =\> r.\_measurement == fcTemperature and (r.\_field == “value”))  
> |\> window(every: autoInterval)  
> //|\> yield(name: “temp2”)
> 
> tempjoin = join(tables: {key1:temp1, key2:temp2}, on: [“\_time”])  
> |\> yield(name: “tempjoin”)

* * *

2021-04-08T15:30:00Z  
Playing with parameters helped to solve the first issue with No Result.

> fcBucket = “openhab/autogen”  
> fcTimestamp = “Localweatherandforecast\_ForecastHours03\_Timestamp”  
> fcTemperature = “Localweatherandforecast\_ForecastHours03\_Temperature”
> 
> fcTemp = from(bucket: fcBucket)  
> |\> range(start: -12h, stop: now())  
> |\> filter(fn: (r) =\> r.\_measurement == fcTemperature )  
> |\> drop(columns: [“item”, “\_measurement”])
> 
> fcTime = from(bucket: fcBucket)  
> |\> range(start: -12h, stop: now())  
> |\> filter(fn: (r) =\> r.\_measurement == fcTimestamp )  
> |\> drop(columns: [“item”, “\_measurement”])
> 
> join(tables: {fcTemp:fcTemp, fcTime:fcTime}, on: [“\_start”, “\_stop”], method: “inner”)

**root cause:** \_time values were different at milliseconds value (at least by 1ms)

* * *

2021-04-08T17:55:00Z  
Here is now the latest script version.

> fcBucket = “openhab/autogen”  
> fcTimestamp = “Localweatherandforecast\_ForecastHours03\_Timestamp”  
> fcData = “Localweatherandforecast\_ForecastHours03\_Temperature”
> 
> fcValue = from(bucket: fcBucket)  
> |\> range(start: dashboardTime, stop: now())  
> |\> filter(fn: (r) =\> r.\_measurement == fcData )  
> |\> drop(columns: [“item”, “\_measurement”])  
> |\> truncateTimeColumn(unit: 1m)
> 
> fcTime = from(bucket: fcBucket)  
> |\> range(start: dashboardTime, stop: now())  
> |\> filter(fn: (r) =\> r.\_measurement == fcTimestamp )  
> |\> drop(columns: [“item”, “\_measurement”])  
> |\> truncateTimeColumn(unit: 1m)
> 
> join(tables: {fcValue:fcValue, fcTime:fcTime}, on: [“\_time”], method: “inner”)  
> |\> drop(columns: [“\_start\_fcValue”, “\_start\_fcTime”, “\_stop\_fcValue”, “\_stop\_fcTime”])  
> |\> group(columns: [“\_value\_fcTime”], mode:“by”)  
> |\> duplicate(column: “\_value\_fcTime”, as: “\_time\_fcTime”)  
> |\> map(fn: (r) =\> ({ r with \_time\_fcTime: time(v: r.\_time\_fcTime \* 1000000) }))  
> //|\> duplicate(column: “\_time\_fcTime”, as: “\_stop”)  
> //|\> mean(column: “\_value\_fcValue”)  
> //|\> duplicate(column: “\_stop”, as: “\_time”)

This is what I get.

 ![grafik](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/4/4430d8ce2254232c3c640989c74dd49dadf30411.png)

Now creating the mean value from column \_value\_fcValue fails with message: **panic: runtime error: invalid memory address or nil pointer dereference**  
Any suggestions?

---

<div class="post-metadata">

**Author:** ![BeSc](https://avatars.discourse-cdn.com/v4/letter/b/e19adc/32.png) [@BeSc](https://community.influxdata.com/u/BeSc)\
**Post date:** [April 9, 2021, 9:38am UTC](https://community.influxdata.com/t/use-data-from-one-query-to-aggregate-data-from-a-second-query/19284/2 "2021-04-09T09:38:09Z")

</div>

This is now my solution after having read through tutorial [TL;DR InfluxDB Tech Tips – How to Extract Values, Visualize Scalars, and Perform Custom Aggregations with Flux and InfluxDB | InfluxData](https://www.influxdata.com/blog/tldr-tech-tips-how-to-extract-values-visualize-scalars-and-perform-custom-aggregations-with-flux-and-influxdb/) from [InfluxData](https://www.influxdata.com/blog/author/anais/)

> fcBucket = “openhab/autogen”  
> fcTimestamp = “Localweatherandforecast\_ForecastHours03\_Timestamp”  
> fcData = “Localweatherandforecast\_ForecastHours03\_Temperature”
> 
> fcValue = from(bucket: fcBucket)  
> |\> range(start: dashboardTime, stop: now())  
> |\> filter(fn: (r) =\> r.\_measurement == fcData )  
> |\> drop(columns: [“item”, “\_measurement”])  
> |\> truncateTimeColumn(unit: 1m)
> 
> fcTime = from(bucket: fcBucket)  
> |\> range(start: dashboardTime, stop: now())  
> |\> filter(fn: (r) =\> r.\_measurement == fcTimestamp )  
> |\> drop(columns: [“item”, “\_measurement”])  
> |\> truncateTimeColumn(unit: 1m)
> 
> fcTable = join(tables: {fcValue:fcValue, fcTime:fcTime}, on: [“\_time”], method: “inner”)  
> |\> drop(columns: [“\_start\_fcValue”, “\_start\_fcTime”, “\_stop\_fcValue”, “\_stop\_fcTime”])  
> |\> group(columns: [“\_value\_fcTime”], mode:“by”)  
> |\> duplicate(column: “\_value\_fcTime”, as: “\_time\_fcTime”)  
> |\> map(fn: (r) =\> ({ r with \_time\_fcTime: time(v: r.\_time\_fcTime \* 1000000) }))
> 
> fcSum = fcTable  
> |\> reduce(fn: (r, accumulator) =\> ({ \_sum: r.\_value\_fcValue + accumulator.\_sum }), identity: {\_sum: 0.0})
> 
> fcCnt = fcTable  
> |\> reduce(fn: (r, accumulator) =\> ({ \_cnt: 1.0 + accumulator.\_cnt }), identity: {\_cnt: 0.0})
> 
> join(tables: {fcSum:fcSum, fcCnt:fcCnt}, on: [“\_value\_fcTime”])  
> |\> map(fn: (r) =\> ({ r with \_avg: r.\_sum / r.\_cnt }))  
> |\> map(fn: (r) =\> ({ r with \_time\_fcTime: time(v: r.\_value\_fcTime \* 1000000) }))  
> |\> rename(columns: {\_time\_fcTime: “\_time”})  
> |\> drop(columns: [“\_sum”, “\_cnt”])

this is the result:

 ![grafik](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/5/55597e55472fed248864fd2da64ca387ca979110.png)