Operations on two entities from same table

Telegraf
,
1

Dear community,

I have been working on this query for more than a month now. Starting to feel crazy/stupid.
I am sure you can help me with this.
Basically, I am trying to access two entities from the same table and make operations on it.

My goal is to compute the percentage of electricity consumption cost of one device compared to the total cost.
Here is what I believe the final query should look like but it does not work:
SELECT meross_1/total AS result FROM (SELECT sum(max_meross_1) AS meross_1 FROM (SELECT max(value) AS max_meross_1 FROM homeassistant.autogen.EUR WHERE time > now() - 7d AND time < now() AND entity_id='meross_1_daily_cost' GROUP BY time(1d) FILL(null)), SELECT sum(max_total) AS total FROM (SELECT max(value) AS max_total FROM homeassistant.autogen.EUR WHERE time > now() - 7d AND time < now() AND entity_id='daily_cost' GROUP BY time(1d) FILL(null)))
Error is: Request failed with status code 400

However, if I simplify it, this works:
SELECT meross_1/meross_1 AS result FROM (SELECT sum(max_meross_1) AS meross_1 FROM (SELECT max(value) AS max_meross_1 FROM homeassistant.autogen.EUR WHERE time > now() - 7d AND time < now() AND entity_id='meross_1_daily_cost' GROUP BY time(1d) FILL(null)))
And this too:
SELECT total/total AS result FROM (SELECT sum(max_total) AS total FROM (SELECT max(value) AS max_total FROM homeassistant.autogen.EUR WHERE time > now() - 7d AND time < now() AND entity_id='daily_cost' GROUP BY time(1d) FILL(null)))

So I have been trying to super simplify my problem.
This works:
SELECT * FROM homeassistant.autogen.EUR, homeassistant.autogen.W
This does not work:
SELECT * FROM homeassistant.autogen.EUR WHERE EUR.entity_id='daily_cost' OR EUR.entity_id='meross_1_daily_cost'
Error is: Your query is syntactically correct but returned no results

As you can see, I am getting a bit lost decomposing my problem and trying to test bit after bit to make it work.
Thank you!

2

It does not work because you can’t just “concatenate” queries by separating them with a comma, InfluxQL does not have union or join and what you are trying to do is not possible. (you want to perform a join)
below is your query (formatted) with a comment in the invalid point

SELECT 
	meross_1/total AS result 
FROM (
	SELECT 
		sum(max_meross_1) AS meross_1 
	FROM (
		SELECT 
			max(value) AS max_meross_1 
		FROM 
			homeassistant.autogen.EUR 
		WHERE 
			time > now() - 7d 
			AND time < now() 
			AND entity_id='meross_1_daily_cost' 
		GROUP BY 
			time(1d) 
		FILL(null)
	),  --!!! this is not valid
	SELECT 
		sum(max_total) AS total 
	FROM (
		SELECT 
			max(value) AS max_total 
		FROM 
			homeassistant.autogen.EUR 
		WHERE 
			time > now() - 7d 
			AND time < now() 
			AND entity_id='daily_cost' 
		GROUP BY 
			time(1d) 
		FILL(null)
	)
)

InfluxQL cannot solve this problem, you will need to use Kapactior and its tickscript (it’s surely possible) or maybe have a look at flux (I’m not sure if it does this kind of calculation)

3

Okay thank you.
Is it possible to create and call views with InfluxQL? In that case I could create two views and call them in a third query.

4

There is no concept of view, a query in InfluxQL cannot reference more than one table. to achieve waht you want you can use kapacitor to Join the two datasets and save them as a new one.

have a look at the influxQL docs and at the Kapacitor docs