Build sum values from multiple buckets in a single graph

InfluxDB 2
1

Hi
I have two buckets with same structure and I want to build a graph with sum of values from both of them (background: I read values from 2 different power meters and want to build the total sum)

image
image3510×570 132 KB

I can have both values with two lines with

power0 = from(bucket: "power0") 
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["ident"] == "15.7.0") 
  |> keep(columns: ["_time","value"])

power1 = from(bucket: "power1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["ident"] == "16.7.0") 
    |> keep(columns: ["_time","value"])

union(tables: [power0, power1])

But I want to have a single line with sum of both values (“15.7.0” from power0 and “16.7.0” from power1)

how is that possible?

2

Hello @Holger_Seehausen,
You can do:

power0 = from(bucket: "power0") 
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["ident"] == "15.7.0") 
  |> keep(columns: ["_time","value"])
  |> sum() 
power1 = from(bucket: "power1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["ident"] == "16.7.0") 
  |> keep(columns: ["_time","value"])
  |> sum() 

join(tables: {power0: power0, power1: power1}, on: ["_time"])
3

Thanks, after some more investigation I think this is a better solution and more flexible solution, if you need other calculation as well

see here: this returns only the “sum” of the two queries

power0 = from(bucket: "power0")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["ident"] == "15.7.0")
  |> aggregateWindow(every: 5m, fn: mean, createEmpty: false)

power1 = from(bucket: "power1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["ident"] == "16.7.0")
  |> aggregateWindow(every: 5m, fn: mean, createEmpty: false)

join(tables: {power1: power1, power0: power0}, on: ["_start", "_stop", "_field",  "_time"])
  |> map(fn: (r) => ({ r with _value : r._value_power0 + r._value_power1 }))
  |> drop(columns: ["_value_power0", "_value_power1"])
4

Helllo @Holger_Seehausen,
Thanks for sharing!