Group by on multiple columns

#1

When i try to run below query I am getting an error
SELECT mean(usage_idle) from cpu GROUP BY host, time(5s),
ERR: error parsing query: aggregate functions with GROUP BY time require a WHERE time clause.

My idea is to create a CQ with multiple Group by Columns.
Can I get mean(usage_idle)/host with 5 minutes time interval

#2

@sai I don’t fully understand your question. The query above will not work without a where time clause. Something like this should be fine:

SELECT mean(usage_idle) FROM cpu WHERE time > now() - 5m GROUP BY host, time(5s)

As far as the CQ, it will time bound any query you are running with it. Here is some more info on how that works.

#3

Thank you for your reply.

I am trying to create a CQ, to get output as ’ mean(usage_idle) from cpu of every host with time interval of 5 sec.

This is my query
CREATE CONTINUOUS QUERY “cpu_usage_idle_avg9” ON “telegraf” BEGIN SELECT mean(usage_idle) INTO “cpu_usage_idle_avg9” FROM cpu WHERE time > now() - 5m GROUP BY host, time(5s) END

But I am getting out put like this
select * from cpu_usage_idle_avg9
name: cpu_usage_idle_avg9
time host mean


1497979280000000000 host1 99.59839357393105
1497979280000000000 host2 98.39195979982985
1497979290000000000 host1 99.59879638879274
1497979290000000000 host2 98.89447236077427
1497979300000000000 host1 99.3987975969483
1497979300000000000 host2 99.295774647244
1497979310000000000 host1 99.59839357393105
1497979310000000000 host2 96.88128772804775
1497979320000000000 host1 98.89669006918
1497979320000000000 host2 97.59036144586763
1497979330000000000 host1 99.59879638879274
1497979330000000000 host2 97.69076305238721
1497979340000000000 host1 99.49849548832623
1497979340000000000 host2 99.29648241141827
1497979350000000000 host1 99.49899799552445
1497979350000000000 host2 99.19678714784332
1497979360000000000 host1 99.59839357392165
1497979360000000000 host2 99.0963855436614
1497979370000000000 host1 99.59839357393105
1497979370000000000 host2 99.19678714784332
1497979380000000000 host1 99.59919839641957
1497979380000000000 host2 99.1461827200543

Its not giving mean of 5s data. its saving data of every sec.

Could you please help me.

#4

@sai I believe that is every 10 seconds. If you are writing data every 10 seconds (the default with telegraf) then you will only get data out of that query every 10 seconds. That output makes sense to me.