Continuous Query with tags and time()

I am having a Influxdb 1.8 with temperature measurements “temperature” tagged with a location “name”.
I need help to create a continuous query that collects daily temperature sum. The temperature sum is defined as a daily average of temperature exceeding +5 centigrade. i.e if the daily average is 2 degree, the temperature sum is 0 day degree and if the daily average is +8 degree the temperature sum is 3 day degree for that day.
I have made a CQ Query_ave that calculates the daily average temperature and another CQ Query_summa to calculate the temperature sum:

CREATE CONTINUOUS QUERY Query_ave ON saa BEGIN SELECT mean(temperature) INTO historia.ave FROM ruuvi GROUP BY time(1d), “name” END
CREATE CONTINUOUS QUERY Query_summa ON saa BEGIN SELECT mean - 5 INTO historia.lamposumma FROM historia.ave WHERE mean > 5 GROUP BY time(1d), * END

I can get the averages correctly and tagged by “name” but the second query gives nothing.
I tested also the query without having a continuous query, like this

SELECT mean - 5 FROM historia.ave WHERE mean > 5 GROUP BY time(1d), *
ERR: GROUP BY requires at least one aggregate function
SELECT mean - 5 FROM historia.ave WHERE mean > 5 GROUP BY time(1d)
ERR: GROUP BY requires at least one aggregate function
SELECT mean - 5 FROM historia.ave WHERE mean > 5 GROUP BY *

name: ave
tags: name=Kasvihuone
time mean


2021-02-14T00:00:00Z 12.059705630877872
2021-02-15T00:00:00Z 12.600186380801674
2021-02-16T00:00:00Z 12.471571166768754

As you can see, the error asks for an aggregate function, although I have time(1d). Without aggregate function the query succeeds but I need to have time() in a continuous query to automate the daily calculation !?!

I am lost with this one.

I still need help in getting the continuous query to run.

I have tried multiple versions since my last post. i realized that this is a nested query instead of two separate queries. I could not have the query itself grouped by time(). This seems to be a known problem with subqueries. I was hoping to have a continuous query with a RESAMPLE clause but did not succeed.

If I run the query from the CLI (without continuous query) I get the results but calculated over all data and the output with the epoch time stamp.

SELECT (mean - 5) AS lamposumma FROM (SELECT mean(temperature) FROM Olari_saa.kasvukausi.ruuvi_Olari) WHERE mean > 5 GROUP BY “name”
name: ruuvi_Olari
tags: name=
time lamposumma


1970-01-01T00:00:00Z 16.061787905249258

Next I tried to run the query as a continuous query once a day for a daily average and save the result into an exisiting measurement “minmax”. No results in the database.

CREATE CONTINUOUS QUERY Query_lamposumma ON Olari_saa RESAMPLE EVERY 1d FOR 1d BEGIN SELECT (mean - 5) AS lamposumma INTO Olari_saa.historia.minmax FROM (SELECT mean(temperature) FROM Olari_saa.kasvukausi.ruuvi_Olari) WHERE mean > 5 GROUP BY “name” END

I cannot figure out why the continuous query does not work. Please, advice.