How to sum two fields through subqueries

Grafana
1

Hello,

I have an influxDB database where I collect electrical consumption data from a multichannel metering device (Iotawatt) and solar production information from the SolarEdge cloud through API, passed over to the same DB by Home Assistant.
My objective now is to obtain an aggregated (1day) view of my export (negative) and import (positive) electricity energy (kWh), so that I can evaluate the savings obtainable by adopting a battery.
I don’t have data at the main meter, but only the consumption of individual loads (from Iotawatt).
So what I want to do is:

  1. sum instantaneous (1s) values of power consumption (in kW) from all Iotwatt channels
  2. deduct from there the instantaneous (1s) value of solar production (in kW) to obtain an instantaneous (1s) net value (positive = consumption, negative = export to the grid).
  3. aggregate this net power information to come to an energy information in kWh per day.

I have been trying hard to find a solution myself, but I can only achieve 1 and 3. I am not able to achieve step 2 of summing up the two series, so my query ends up in delivering 2 sets that are displayed separately. See example (in this case I used 1m for step #3, to see more details).

Any help will be greatly appreciated! Probably the problem is coming from the way the data flows into the DB, but I did not pay too much attention when I created the setup… Now it’s late.

Regards
Guido

Query:
SELECT (mean("sommapotenza")-mean("sun")) /60 AS "net" FROM ( SELECT sum("Watts") AS "sommapotenza" FROM "autogen"."IotaWatt" WHERE $timeFilter GROUP BY time(1s) fill(none) ), ( SELECT mean("value") AS "sun" FROM "autogen"."W" WHERE ("entity_id" = 'solaredge_current_power') AND $timeFilter GROUP BY time(1s) fill(linear) ) WHERE $timeFilter GROUP BY time(1m) fill(0)

Result: (as you can see, NET is split in 2, one being the sum of Iotawatt channels and the other being the Solaredge info).

image
image820×211 73.7 KB

2

@sturmenstrudel that’s a really cool use case! We use Grafana + Influx to work with renewable energy data also.

That said, the Influx query language isn’t so great for doing calculations. Specifically, you can’t do mathematical operations across measurements, as I think you are trying to (https://github.com/influxdata/influxdb/issues/3552). Your main options are either:

Hope that helps!

3

Hi, thanks for your answer, it clarifies the limitations I found and gives some workaround.
I was thinking: if I call my measurement “power” and I define different fields corresponding to the different units possible for a power measurement (e.g. “W”, “Watts”, “kW”…), would it then be possible to sum across fields (and eventually applying a math operator to convert kW in W, as they need to be homogeneous when summed up).
If I read this document properly, I understood that this can be done:
https://docs.influxdata.com/influxdb/v1.7/guides/calculating_percentages/

Regards
Guido

4

Yep, that would work. Just put the different values you need to work with in separate fields within the same measurement.

To give an analogy, recording data in different fields within the same measurement in Influx is like putting it in different columns of a table in Excel (or different columns in a SQL database table). You can then easily calculate derived quantities based on two or more of these raw-data columns.

5

Great, that should work.
I will rebuild the database in a different scheme by doing queries such as:
SELECT value AS kW INTO “homeassistant”.“autogen”.“power” FROM “homeassistant”.“autogen”.“kW” GROUP BY *

I will have to do some cleanup for all this redundant data, but I tested for one measurement and it does what it is supposed to do. Thanks!