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?

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)

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?

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)

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…

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?

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?

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) ?

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)
)

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.