ERR: mixing aggregate and non-aggregate queries is not supported

1

I set up a weather station with a rain gauge, which is writing data into an Influx 1.7.7 DB.
For this gauge I 'm collecting data into a 7 day shard with a value every 10 seconds.
I set up a continuous query after collecting data for 3 days like this:

CREATE CONTINUOUS QUERY collectd_cq_10min_sum ON collectd_db BEGIN SELECT sum(value) AS value INTO collectd_db."ten_min_sum".rain FROM collectd_db."default".rain GROUP BY time(10m), * END

Now I’d like to get the 3 days into the database as well.
I tried this:

SELECT sum(value),host,region,unit,unit_type,instance  AS value INTO collectd_db."ten_min_sum".rain FROM collectd_db."default".rain where time >= now() - 7d GROUP BY time(10m)

which returns:
ERR: mixing aggregate and non-aggregate queries is not supported

If I do this:

SELECT sum(value)  AS value INTO collectd_db."ten_min_sum".rain FROM collectd_db."default".rain where time >= now() - 7d GROUP BY time(10m)

The values get written, but I lose all other information like host and unit. See below:

time                host        instance region unit unit_type value
----                ----        -------- ------ ---- --------- -----
1563003000000000000                                            0
1563003600000000000 weather     rain     europe ml   volume    0

How can I get at least the host name into the database?

Thanks a lot in advance.

2

You can try using the “last” function for the host.

Something like

SELECT sum("value") AS "value", last("host") AS "host", last("region") AS "region", last("instance") AS "instance" INTO "database"."retentionpolicy".measurement FROM "database"."autogen"."measurement"')

I don’t know a great deal about CQ’s, i use Kapacitor but the query should be similar. You might not need the double quotes if you’re defining it within Influx as a CQ.

3

Thank you for your reply.
Unfortunately this does not seem to work.

I tried the following:

SELECT sum(value) as value, last(host) as host FROM collectd_db."default".rain where value <>0 GROUP BY time(10m) LIMIT 2

and got:

time                value  host
----                -----  ----
1562994000000000000 0.5588
1562994600000000000 0.5588

I tried to replace last() with several other functions which never resulted in a value for the host.

4

Actually, looking at it. If you are using CQ’s then it might just be a case of grouping by your tags.

I’ve just had a look through here

If think if you group by your tags, or all tags then they should be retained.

SELECT sum(value)  AS value INTO collectd_db."ten_min_sum".rain FROM collectd_db."default".rain where time >= now() - 7d GROUP BY time(10m), *

The difference between this and your original (third query) is the: GROUP BY time(1m), *

I think that might work

5

How could I not have seen this? The GROUP BY time(10m), * really made the difference.
Thank you very much.

6

No problem @Rayn0r! You’re welcome.