Adding multiple fields matching a regular expression

InfluxDB 1
1

Hi all, using InfluxQL v1 I am trying to sum field values from multiple fields, over a given time range. I am trying this query:

SELECT sum(/^cumulus|rad|sdb$/) as Infra
  FROM iota_06.rt_energy.energy WHERE time >= now() - 24h and time <= now() fill(0)

… but this yields 3 columns (Infra_cumulus, Infra_rad, Infra_sdb) - I would simply like them those 3 columns summed up… any suggestion?

I sent through the manual & the examples I could find but I can’t figure out how to write the query.

Thanks!

2

Hello @franck102,
It’s good to see you back!

hmm its been so long since I’ve used InfluxQL lol. Most of our users use 2.x and Flux now.
are Infra_cumulus, Infra_rad, Infra_sdb all fields?
I think it would look like:

SELECT sum_1 + sum_2
    (SELECT SUM("field1") AS sum_1, SUM("field2") AS sum_2 FROM iota_06.rt_energy.energy WHERE time >= now() - 24h and time <= now() fill(0))

But I’m not 100% sure…(yikes it’s been a while) Can you please give it a try and let me know?

Alternatively if you’re using 1.x, then you can try the following flux query (just fyi).

from(bucket: "energy ")
  |> range(start:-24h)
  |> filter(fn: (r) => r["_measurement"] == " iota_06.rt_energy")
  |> filter(fn: (r) => r["_field"] == "field1" or  r["_field"] == "field2")
  |> group()
  |> sum()
3

Sorry I missed your reply, I just tried your subquery today and it does work like a charm… thanks a lot!

Franck

1 Like
4

Regarding InfluxDB 2 btw, I have an old device that would require flashing to support writing to influx 2, and I haven’t found the time/motivation to get into it…

5

@franck102 yay! I’m happy it worked for you.

What are you doing with InfluxDB? It really makes my day to learn about usecases rather than helping people without the satisfaction of the context. But…if that’s not enough motivation, you can also get a free hoodie:

6

I am tracking electrical PV production and usage using an Iotawatt device (https://iotawatt.com/; and data from an Okofen pellet heater.

Works like a charm, although fitting on the 1Gb RAM of my raspberry pi requires some retention policy tuning…