Divide results returned from two subqueries

InfluxDB 2
1

I would like to calculate the ratio of two values, each returned by a subquery. If I execute them separately, the returned data looks good:

1st Query:

SELECT max(“value”)-min(“value”) AS verbrauch FROM StromzaehlerkWh WHERE time > now()-7d ;

┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┓
┃ index ┃         time          ┃  verbrauch   ┃
┣━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━┫
┃      1┃  1681998913.0000000000┃  8.8686000000┃
┣━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━┫

2nd Query:

SELECT max(“value”)-min(“value”) AS Einspeisung FROM “StromzaehlerEinspeisungkWh” WHERE time > now()-7d ;

┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┓
┃ index ┃         time          ┃  Einspeisung   ┃
┣━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━┫
┃      1┃  1681998164.0000000000┃  149.3115000000┃
┣━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━┫

But if i try to combine them together, I get a nil value

SELECT Einspeisung, Verbrauch FROM
(SELECT max(“value”)-min(“value”) as Verbrauch FROM StromzaehlerkWh WHERE time > now()-7d ) ,
(SELECT max(“value”)-min(“value”) AS Einspeisung FROM “StromzaehlerEinspeisungkWh” WHERE time > now()-7d );

┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┓
┃ index ┃         time          ┃  Einspeisung   ┃ Verbrauch ┃
┣━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━╋━━━━━━━━━━━┫
┃      1┃  1681998466.0000000000┃  149.8254000000┃      <nil>┃
┣━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━┻━━━━━━━━━━━┫

The result is also the same, if I put the WHERE clause to the outside select.

SELECT Einspeisung, Verbrauch FROM
( SELECT max(“value”)-min(“value”) AS Verbrauch FROM StromzaehlerkWh ) ,
(SELECT max(“value”)-min(“value”) AS Einspeisung FROM “StromzaehlerEinspeisungkWh”)
WHERE time > now()-7d ;

┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┓
┃ index ┃         time          ┃  Einspeisung   ┃ Verbrauch ┃
┣━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━━━━━━╋━━━━━━━━━━━┫
┃      1┃  1681998779.0000000000┃  150.2276000000┃      <nil>┃
┣━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━━━━━━┻━━━━━━━━━━━┫

Any hint, what I am doing wrong?

2

Hello @monster_marshmellow,
I don’t think you can select for data from two different measurements with InfluxQL. This has been a long time request which is a contributing factor in why Influx created Flux in 2.x. Now we’re moving to SQL which also supports joins.
If you’re using OSS you can try to use Flux.
Or you can sign up for a free tier Cloud account and use SQL and joins. Otherwise you could query the data with a client library with two calls and join the data together in the language of your choice. I’m sorry I can’t provide the answer you’re looking for.

3

Thanks for that information. I will give Flux a try.