Add kWh (from watt_seconds) of multiple entities returns 0

Grafana InfluxDB
1

I’m using Grafana in Home Assistant with InfluxDB. I have a Brultech Green Energy Monitor attached to which is recording entities in InfluxDB, with fields “value” (which are watts) and “watt_seconds” which in the accumulated watts over time. i.e. monotonically increasing.

I’m trying to create a bar graph of kWh over a time period of multiple entries, and a bar for the sum of all entries.

for this example graph I have 2 entries, “cabana” and “pool”

SELECT spread(“watt_seconds”) / 3600000 FROM “W” WHERE (“entity_id” = ‘cabana’) AND $timeFilter fill(null)

SELECT spread(“watt_seconds”) / 3600000 FROM “W” WHERE (“entity_id” = ‘pool_parcel’) AND $timeFilter fill(null)

for the total I’m using subqueries

SELECT (“a” + “b”) / 3600000 FROM (
SELECT spread(“watt_seconds”) AS “a” FROM “W”
WHERE (“entity_id” = ‘cabana’) AND $timeFilter fill(0)
),(
SELECT spread(“watt_seconds”) AS “b” FROM “W”
WHERE (“entity_id” = ‘pool_parcel’) AND $timeFilter fill(0)
)

But I get 0 for a result.

If I use SELECT (“a” + “a”), I get 2 * “a”, and if I use SELECT (“b” + “b”), I get 2 * “b”, as expected. but adding “a” + “b”, I get 0.

Any suggestions what I’m doing wrong.

Thanks

2

I figured it out. Return all subqueries as the same alias, and sum them

SELECT sum(“a”) / 3600000 FROM (
SELECT spread(“watt_seconds”) AS “a” FROM “W”
WHERE (“entity_id” = ‘cabana’) AND $timeFilter fill(null)
),(
SELECT spread(“watt_seconds”) AS “a” FROM “W”
WHERE (“entity_id” = ‘pool_parcel’) AND $timeFilter fill(null)
) WHERE $timeFilter