Subquery group by time

,
1

I’m using influx 1.7 and have a question about aggregating results of a subquery.
The result of the subquery is this:

name: measurements
tags: sensorId=sensor1
time                      value
----                      -----
2020-06-13T02:00:00+02:00 10496.30479359464
2020-06-13T03:00:00+02:00 10818.674817088533
name: measurements
tags: sensorId=sensor2
time                      value
----                      -----
2020-06-13T02:00:00+02:00 20452.49079731096
2020-06-13T03:00:00+02:00 21042.71584202803

I wish to sum the data over tags, but keep the time grouping.
Required result:

name: measurements
time                      value
----                      -----
2020-06-13T02:00:00+02:00 30948.7955909
2020-06-13T03:00:00+02:00 31861.3906591

Somehow if I wrap the subquery, it returns 480 results.
Wrapping query:

select sum(value) as value
from(
  <<subquery>>
)
where
  time >= '2020-06-13T00:00:00Z' and
  time <  '2020-06-13T02:00:00Z'
group by
  time(1h)
tz('Europe/Amsterdam')

The first few lines of the actual result are:

name: measurements
time                      value
----                      -----
2020-06-13T02:00:00+02:00 287.94145560581165
2020-06-13T03:00:00+02:00 0
2020-06-13T02:00:00+02:00 562.0502567542244
2020-06-13T03:00:00+02:00 0
2020-06-13T02:00:00+02:00 335.88116890711143
2020-06-13T03:00:00+02:00 0
2020-06-13T02:00:00+02:00 651.9935981011204
2020-06-13T03:00:00+02:00 0
2020-06-13T02:00:00+02:00 340.9463575063458
2020-06-13T03:00:00+02:00 0
... (470 more lines)

Can someone help me to get to the required result? And maybe explain what is happening that returns the 480 results?
I posted a simplified version of the query, as this is the part that is still failing. If more details are required, I am happy to share.

2

Hello @millitza,
Welcome!
Summing across tags at the same timestamp is something you’ll have to do in Flux (InfluxQL is quite limited in its capabilities. It can feel like a bit ask to switch over, but now that I’ve gotten used to Flux I wonder about how I ever managed with InfluxQL :p).
I recommend enabling flux if you’re working with v1.7.6+. Then you can |> group(columns: ["_time"]) |>sum()

Please let me know how you’re getting along.

3

Hi @Anaisdg,

Thank you so much for the quick reply! I will try out Flux and will let you know if this solved the issue. Installing should not be an issue as we run v1.7.8.

1 Like