# Elasticsearch SQL in Kibana
**URL:** https://discuss.elastic.co/t/elasticsearch-sql-in-kibana/193864
**Category:** Kibana
**Created:** [August 5, 2019, 6:31pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-in-kibana/193864 "2019-08-05T18:31:06Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@cbmurphy87](https://discuss.elastic.co/u/cbmurphy87)
#### Post date: [August 5, 2019, 6:31pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-in-kibana/193864/1 "2019-08-05T18:31:07Z")
I'm trying to do a SUM on a query, and can't seem to get anything to work. Perhaps I'm using the wrong syntax, but I keep getting the error message "Cannot use non-grouped column".
I'm using metricbeat to collect metrics on some systems. The fields I'm interested in are "host.name" and "system.diskio.iostat.read.request.per\_sec". I want to sum each host's system.diskio.iostat.read.request.per\_sec value to give me a total IOPS across all hosts. The query I'm trying to run is as follows:
"SELECT host.name as hostname, SUM(system.diskio.iostat.read.request.per\_sec) as IOPS, "@timestamp" as time FROM "metricbeat-\*""
I'm not sure if my syntax is wrong, or if I need to do some grouping by time stamp, or what.
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [August 5, 2019, 6:48pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-in-kibana/193864/2 "2019-08-05T18:48:48Z")
try something like this. I am not an expert in sql
select host.name, sum(xyz) from metricbeat-\* group by host.name
grou by this will sum for a host, then second host and so on.
you can't have timestamp there. if you want for particular date then you have to do
select host.name,sum(xyz) from metricbeat-\* where date = xyz group by host.name
---
### Author:  [@cbmurphy87](https://discuss.elastic.co/u/cbmurphy87)
#### Post date: [August 5, 2019, 7:27pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-in-kibana/193864/3 "2019-08-05T19:27:52Z")
I don't want the average over a particular day, I want to return values at all times so I can graph it. I just want to aggregate the values from each host at any particular timestamp. It works fine graphing them individually, I just want to sum all the individual hosts together.
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [August 6, 2019, 2:14pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-in-kibana/193864/4 "2019-08-06T14:14:23Z")
but I think group by does same
for example
host1 - 100
host1 - 100
host2 - 200
host2 - 200
then when you do group by you can get
host1 - 200
host2 - 400
---
### Author:  [@cbmurphy87](https://discuss.elastic.co/u/cbmurphy87)
#### Post date: [August 6, 2019, 8:14pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-in-kibana/193864/5 "2019-08-06T20:14:06Z")
But that isn't what I want to do. I want to add host1 and host2 at every timestamp, giving 300 for each timestamp. I want the aggregate IOPS of all hosts at any particular time, and then graph that over time. Basically, I have a cluster that I want to get the total IOPS of the cluster, but each node is reported individually by metricbeat.
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [August 14, 2019, 5:28pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-in-kibana/193864/6 "2019-08-14T17:28:34Z")
oh then you do group by timestamp
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [September 11, 2019, 5:28pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-in-kibana/193864/7 "2019-09-11T17:28:35Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.