InfluxQL Aggregration shows too high value

Grafana InfluxDB
1

  • What Grafana version and what operating system are you using?
    V9.2.4 as an Home Assistant add-on running on Debian with InfluxDB v1.8.9. as docker on Synology NAS using influxQL.

  • What are you trying to achieve?
    Use a subquery to sum measurements and then aggregate/group by 1 hour

  • How are you trying to achieve it?

SELECT max("SOU") + max("GF") + max("US") + max("GUEST") FROM
( SELECT max("value") AS "SOU" FROM "kW" WHERE ("entity_id" = 'heater_meters_sou_power') AND $timeFilter GROUP BY time(1m) fill(null) ),
( SELECT max("value") AS "GF" FROM "kW" WHERE ("entity_id" = 'heater_meters_gf_power') AND $timeFilter GROUP BY time(1m) fill(null) ),
( SELECT max("value") AS "US" FROM "kW" WHERE ("entity_id" = 'heater_meters_us_power') AND $timeFilter GROUP BY time(1m) fill(null) ),
( SELECT max("value") AS "GUEST" FROM "kW" WHERE ("entity_id" = 'heater_meters_guest_power') AND $timeFilter GROUP BY time(1m) fill(null) )
GROUP BY time(10m) fill(null)

Using other fill() options does not help.

  • What happened?
    On 14:33 it can be seen that the sum of the 4 values is 16.3, which is the highest in that hour.
    Still, on 14:30 the aggregated sum value is 20.7.

  • What did you expect to happen?
    That the sum value shows 16.3.
    Or, that I understand what I’m doing wrong :innocent:

  • Can you copy/paste the configuration(s) that you are having problems with?
    See above.

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    No.

  • Did you follow any online instructions? If so, what is the URL?
    No.

Grafana Screenshot
Grafana Screenshot1161Ă—675 58.6 KB

Any help is greatly appreciated!

(PS I have searched, but couldn’t find an existing topic)

2

You have grouping by 1m in subqueries and 10m in main query but your question is by 1 hour

3

Hi yosiasz, thanks for the quick reply.

The subquery is per minute, since I want to sum per minute.
The main query GROUP BY should have been indeed per hour, the 10 minutes was for “debugging”.

1 Like
4

Did some more investigation.
Different data source, but still see a weird thing:
Summung up from the first graph I get to 30, but in the second graph it shows 50…

Grafana Screenshot2
Grafana Screenshot21227Ă—599 36.6 KB

Grafana Screenshot3
Grafana Screenshot31205Ă—589 36.6 KB

5

If your time stamp utc?

6

“Default” = UTC+1
Minimum 20 characters.

7

In influx db is what I meant :slight_smile:

Also why are you using spread

8

The data is an increasing energy consumption, so I use spread to get the difference (consumption) per “bucket”, in this case 1 day.
Is this incorrect?

I’m assuming it’s in UTC since after some googling it seems that’s the default and AFAIR K haven’t changed it. How can I check?

1 Like