Sum multiple queries

Fluxlang
1

i have several energy meters and want to get the total consumption using flux but i dont get it, any hint ?
I simply want to get the sum of p1,p2 and p3 (all 3 and also the total) for all enrgy meters. The timestamp is the same for all meters as i write it all at the same time with the same timestamp using GitHub - ardiehl/emModbus2influx: Modbus to InfluxDB (v1/v2) and/or MQTT

This is my source for 3 meters (i have more)

g1 = from(bucket: “ad”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “energyMeter”)
|> filter(fn: (r) => r[“Device”] == “G1”)
|> filter(fn: (r) => r[“_field”] == “p1” or r[“_field”] == “p2” or r[“_field”] == “p3”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

g2 = from(bucket: “ad”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “energyMeter”)
|> filter(fn: (r) => r[“Device”] == “G2”)
|> filter(fn: (r) => r[“_field”] == “p1” or r[“_field”] == “p2” or r[“_field”] == “p3”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

g3 = from(bucket: “ad”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “energyMeter”)
|> filter(fn: (r) => r[“Device”] == “G3”)
|> filter(fn: (r) => r[“_field”] == “p1” or r[“_field”] == “p2” or r[“_field”] == “p3”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

2

Welcome @Armin_Diehl

You can have one query with a group() and sum() function. I do not see why you would need the aggregateWindow() function.

from(bucket: "junkbucket")  // change this to your bucket name
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "energyMeter")
  |> filter(fn: (r) => r["Device"] == "G1" or r["Device"] == "G2" or r["Device"] == "G3")
  |> filter(fn: (r) => r["_field"] == "p1" or r["_field"] == "p2" or r["_field"] == "p3")
  |> group(columns: ["_field", "_measurement"], mode:"by")  
  |> sum(column: "_value")  
  |> yield(name: "sum_of _field_values")

gives us the totals for p1, p2 and p3:

image
image1257×524 39.8 KB 

from(bucket: "junkbucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "energyMeter")
  |> filter(fn: (r) => r["Device"] == "G1" or r["Device"] == "G2" or r["Device"] == "G3")
  |> filter(fn: (r) => r["_field"] == "p1" or r["_field"] == "p2" or r["_field"] == "p3")
  |> group(columns: ["Device", "_measurement"], mode:"by")  
  |> sum(column: "_value")  
  |> yield(name: "sum_of _field_values_by_device")

gives us the totals for G1, G2 and G3:

image
image1172×531 40.6 KB

If you want the grand total, just insert another group() and sum() function like this:
image

3

Thnaks a lot, that pointed me to the solution. However, i dont wnat to have the totals but the totals of all power per time, this woked for me:

from(bucket: "ad")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "energyMeter")
  |> filter(fn: (r) => r["Device"] == "Allgemein" or r["Device"] == "G1" or r["Device"] == "G2" or r["Device"] == "HK" or r["Device"] == "Halle")
  |> filter(fn: (r) => r["_field"] == "p1" or r["_field"] == "p2" or r["_field"] == "p3")
  |> group(columns: ["_time","_field"])
  |> sum(column: "_value")
  |> group(columns: ["_value", "_time"], mode: "except")
  |> yield(name: "sum_of_all_meters_per_phase")

image
image972×263 40 KB

and for the sum of all phases

from(bucket: "ad")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "energyMeter")
  |> filter(fn: (r) => r["Device"] == "Allgemein" or r["Device"] == "G1" or r["Device"] == "G2" or r["Device"] == "HK" or r["Device"] == "Halle")
  |> filter(fn: (r) => r["_field"] == "p1" or r["_field"] == "p2" or r["_field"] == "p3")
  |> group(columns: ["_time"])
  |> sum(column: "_value")
  |> group(columns: ["_value", "_time"], mode: "except")
  |> set(key: "_field", value: "p")
  |> yield(name: "sum_of_all_meters_and_phases")

however, here the set does not work in grafana, the field name is shown as _value ?

4

image
image866×356 30.8 KB

5

Try this…

|> rename(columns: {_value: "something"})

6

works fine, thanks for your support

7

@Armin_Diehl Please mark the above as Solution if it indeed resolved your question, so that others may find it in the future.