Influxdb subquery Issue - wrong sum

1

I have a strange problem: in grafana I have the following query for a singlestat:

SELECT ( sum(“zon”) + sum(“export”))
FROM (
SELECT sum(“kWh”) AS “zon” FROM “iotawatt” WHERE (“ct” = ‘ZonkWh’) AND $timeFilter
) ,(
SELECT sum(“kWh”) AS “export” FROM “iotawatt” WHERE (“ct” = ‘EandiskWh’) AND $timeFilter AND “kWh” < 0
)
GROUP BY time(1h) fill(null)

So nothing fancy i guess, just 2 queries and i want the sum of both.

However, this results in 9.46kWh where zon = 16.92kWh and export is -6.54kWh
So the result should be 10.38 (16.92 + -6.54)
What am I doing wrong?

2

Well, firstly, what happens if you temporarily set up two extra singlestat
queries:

  1. SELECT sum(“kWh”) AS “zon” FROM “iotawatt” WHERE (“ct” = ‘ZonkWh’) AND
    $timeFilter ) GROUP BY time(1h) fill(null)

  2. SELECT sum(“kWh”) AS “export” FROM “iotawatt” WHERE (“ct” = ‘EandiskWh’)
    AND $timeFilter AND “kWh” < 0 ) GROUP BY time(1h) fill(null)

Do those queries give you the results you would expect?

Secondly, does the discrepancy vary depending on what your $timeFilter happens
to be (and, specifically, more or less than 1 hour)?

Antony.

3

Those 2 queries give the results i expect: 16.92 and 6.54.
And yes, the discepancy varies with the $timefilter ex: 10s gives 6.91kWh

For now i “solved the problem” by using the Math Singlestat, but i guess it should work with subqueries as well?

4

Okay, that latter is (I think) very useful to know.

What happens with:

SELECT ( sum(“zon”) + sum(“export”))
FROM (
SELECT sum(“kWh”) AS “zon” FROM “iotawatt” WHERE (“ct” = ‘ZonkWh’) AND
$timeFilter GROUP BY time(1h)
) ,(
SELECT sum(“kWh”) AS “export” FROM “iotawatt” WHERE (“ct” = ‘EandiskWh’)
AND $timeFilter AND “kWh” < 0 GROUP BY time(1h)
)
GROUP BY time(1h) fill(null)

?

Antony.

5

Unfortunately Nothing changed, it keeps reading 9.46kWh

6

I don’t fully understand what you are doing, but if the inner queries each return just one value why are you using sum( ) again in the outer query?

7

I’m quite new to influxdb/grafana so i found the example about subqueries.
I just want the sum of the 2. (Timeseries is set to now/d to force it to calculate for the current day so far.
If i change ‘sum’ to ‘mean’ in the outer query, the result stays the same.
Maybe i’m using it wrong?

8

If changing “sum” to “mean” gives the same result, then there is definitely
something very wrong.

I can’t think of any numbers which would sum to the same value as their mean
unless the result is zero.

However, I agree with Clanlaw - why “sum” a single value?

What does the following give:

SELECT ( zon + export)
FROM (
SELECT sum(“kWh”) AS “zon” FROM “iotawatt” WHERE (“ct” = ‘ZonkWh’) AND
$timeFilter
) ,(
SELECT sum(“kWh”) AS “export” FROM “iotawatt” WHERE (“ct” = ‘EandiskWh’)
AND $timeFilter AND “kWh” < 0
)
GROUP BY time(1h) fill(null)

Antony.

9

Yes, very strange indeed…
When changing it to SELECT(zon + export ) or SELECT (“zon” + “export”) it keeps N/A (but no error)
This was why i used the mean or sum…

10

If the inner queries are returning only one number each then sum(zon) is the same as mean(zon) is the same as zon.
If you remove the sum at the outer level do you have to remove the outer GROUP BY too?

11

If i remove the GROUP BY time(1h) fill(null) at the outer (and put it in the subqueries) the total gives 0

12

To try and simplify the problem what do these show

SELECT sum(“kWh”) AS “zon” FROM “iotawatt” WHERE (“ct” = ‘ZonkWh’) AND
$timeFilter GROUP BY time(1h)
)

and

SELECT ( zon )
FROM (
SELECT sum(“kWh”) AS “zon” FROM “iotawatt” WHERE (“ct” = ‘ZonkWh’) AND
$timeFilter GROUP BY time(1h)
)
13

If the inner queries are returning only one number each then sum(zon) is
the same as mean(zon) is the same as zon.

Well, obviously the “sum” of one number is the same as the “mean” of that
single number, but I meant the sum and mean of more than one number (which is
how the functions are normally used).

If you remove the sum at the outer level do you have to remove the outer
GROUP BY too?

Sounds worth a try…

Antony.

14

Both folowing statements give the correct values.

SELECT (zon)
FROM (
SELECT sum(“kWh”) AS “export” FROM “iotawatt” WHERE (“ct” = ‘EandiskWh’) AND $timeFilter AND “kWh” < 0 GROUP BY time(1h)
) ,(
SELECT sum(“kWh”) AS “zon” FROM “iotawatt” WHERE (“ct” = ‘ZonkWh’) AND $timeFilter GROUP BY time(1h)
)

and

SELECT (export )
FROM (
SELECT sum(“kWh”) AS “export” FROM “iotawatt” WHERE (“ct” = ‘EandiskWh’) AND $timeFilter AND “kWh” < 0 GROUP BY time(1h)
) ,(
SELECT sum(“kWh”) AS “zon” FROM “iotawatt” WHERE (“ct” = ‘ZonkWh’) AND $timeFilter GROUP BY time(1h)
)

but if i use

SELECT (zon + export )
FROM (
SELECT sum(“kWh”) AS “export” FROM “iotawatt” WHERE (“ct” = ‘EandiskWh’) AND $timeFilter AND “kWh” < 0 GROUP BY time(1h)
) ,(
SELECT sum(“kWh”) AS “zon” FROM “iotawatt” WHERE (“ct” = ‘ZonkWh’) AND $timeFilter GROUP BY time(1h)
)

i get 0 kWh, so the problem seems to be in the (zon + export) ?

15

How about

SELECT (sum(data) )
FROM (
SELECT sum(“kWh”) AS “data” FROM “iotawatt” WHERE (“ct” = ‘EandiskWh’) AND $timeFilter AND “kWh” < 0 GROUP BY time(1h)
) ,(
SELECT sum(“kWh”) AS “data” FROM “iotawatt” WHERE (“ct” = ‘ZonkWh’) AND $timeFilter GROUP BY time(1h)
)
16

thats seems to be correct! :smiley:
could you explain why this works?

17

I can’t explain why the other idea doesn’t.
With this one both sub-queries return a value with the same name, so it is not necessary to do sum(x+y) since x and y are both ‘data’. You could use any name for data.

18

thanks, most important is that it works :wink: