How do I get top 5 results in influxdb after sum of values over a period

Question

Given below measurement in InfluxDB, I want to display a table in Grafana with a Top 2 results consisting of most frequent occurring device for a given sensor. So expected result for the below data would be a table with 2 tows of

device                               total
------                               ----
345678                               7
234567                               3

> select * from incoming_events_by_device limit 50;
name: incoming_events_by_device
time                device                               host   metric_type sensor    value
----                ------                               ----   ----------- ------    -----
1535575550000000000 123456                               foo.com counter     efg       1
1535575550000000000 123456                               foo.com counter     efg       1
1535575550000000000 234567                               foo.com counter     efg       1
1535575550000000000 234567                               foo.com counter     hij       2
1535575550000000000 234567                               foo.com counter     efg       1
1535575550000000000 345678                               foo.com counter     nice      1
1535575550000000000 358168                               foo.com counter     nice      1
1535575550000000000 345678                               foo.com counter     nice      1
1535575550000000000 345678                               foo.com counter     nice      4
1535575550000000000 345678                               foo.com counter     efg       1
1535575550000000000 345678                               foo.com counter     efg       12

I have tried using sub query like this but not getting the expected result. It shows empty response.

SELECT top(incoming_devices_count,5) FROM (SELECT sum(device) as incoming_devices_count FROM incoming_events_by_device WHERE sensor='nice' AND time > now() - 30m group by device)

Answer 1

Found the query.

select top(total, 3), device from (select sum(value) as total from incoming_events_by_device where sensor='nice' AND time > now() - 30m group by device);