Influx: Create sum of a query grouped by tag

Grafana InfluxDB
1

Hi all,

I am using Influx 1.8 with InfluxQL. Below you can see in the top row of the gauges the result of the query ‘loadpoints’

image
image2174×2432 437 KB

This query is an integral over time that is grouped by a loadpoint tag:
SELECT integral("value") / 3600 FROM "chargePower" WHERE $timeFilter GROUP BY time(1d), "loadpoint"::tag fill(none) tz('$timezone')

Therefore it creates two tables for each loadpoint:

image
image2146×652 41.4 KB

Now I want to create the sum of each loadpoints integral (I need that sum later in another calculation). Is there a way how I can achieve this?

Previously I had two queries, each one filtering for one of the loadpoints. However I don’t know how many loadpoints there are and the values of the loadpoint tags. Number of loadpoints and names can be arbitrary.

Is there a way to reference all resulting groups from one query and sum these up? I tried the ‘Reduce’ function, but obviously it didn’t create the desired result as it threads the resulting groups as separate tables.

  • What Grafana version and what operating system are you using?
    Grafana 11.5.1 on Linux
2

I would sum it on the InfluxDB level - that’s faster option generally, e.g.:

SELECT SUM("integral") FROM
(
  SELECT integral("value")  / 3600 FROM "chargePower" WHERE $timeFilter GROUP BY time(1d), "loadpoint"::tag fill(none) tz('$timezone')
)

This is just example idea - fix any syntax, functional issues in my Influx QL based on your need.

2 Likes
3

Thank you very much. That almost nails it. However now I have another problem. The time got lost in the outer query and therefore I can no longer use it with other metrics in the following Math expression:

image
image2886×1132 197 KB

Any idea how I can keep the timestamp of the integrals?

4

Got it. Just added a time grouping

SELECT SUM("integral") FROM
(
  SELECT integral("value")  / 3600 FROM "chargePower" WHERE $timeFilter GROUP BY time(1d), "loadpoint"::tag fill(none) tz('$timezone')
) GROUP BY time(1d) tz('$timezone')

Thank you very much. Saved my day!