SELECT with subquery does not merge values from multiple measurements

Hi,

I need to be able to get the total value of a field across multiple measurement series.
The query I have come up with is:

select sum(e_max) as eTotal from ( select max(eToday) as e_max from /.*/ where __devhash =~ /(?:dev1|dev2|dev3)/ and time>1491402600s group by __devhash order by time desc limit 1 )

The pattern to select devices (dev1|dev2|dev3) could have one or many devices listed, and the devices might not all be in the same measurement. (This application uses measurements with names like ‘inverter’, ‘usagemeter’, ‘battery’, etc.)

This works fine when dev1, dev2, dev3 etc are all in the same measurement.
However if the listed devices are in different measurements I get a separate SUM() for each measurement.
For example,

> SHOW SERIES
key
---
inverter,__devhash=INVERTER_TEST_1
inverter,__devhash=I_American_32
inverter,__devhash=I_Australian_31
inverter,__devhash=I_European_30
inverter,__devhash=I_Super_1309
usagemeter,__devhash=USAGE_METER_TEST_1

> select sum(e_max) as eTotal from ( select max(eToday) as e_max from /.*/ where __devhash =~ /(?:USAGE_ME|INVERTER)/ and time>1491402600s group by __devhash order by time desc limit 1 )
name: inverter
time eTotal
---- ------
0    56.929

name: usagemeter
time eTotal
---- ------
0    56.385

> select sum(e_max) as eTotal from ( select max(eToday) as e_max from /.*/ where __devhash =~ /(?:I_Amer|I_Aus|I_Eur)/ and time>1491402600s group by __devhash order by time desc limit 1 )
name: inverter
time eTotal
---- ------
0    649.9369999999999

> 

Is this expected behaviour ?

Thanks
Jeremy Begg

So unfortunately we can’t do this at the moment…it’s been a WIP for a very long time and hopefully will get on the radar shortly!

1 Like