# Cannot use non-grouped column error. How to get around this?

**URL:** <https://discuss.elastic.co/t/cannot-use-non-grouped-column-error-how-to-get-around-this/208579>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [November 19, 2019, 8:33pm UTC](https://discuss.elastic.co/t/cannot-use-non-grouped-column-error-how-to-get-around-this/208579 "2019-11-19T20:33:10Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![pgervais](https://avatars.discourse-cdn.com/v4/letter/p/65b543/32.png) [@pgervais](https://discuss.elastic.co/u/pgervais)\
**Post date:** [November 19, 2019, 8:33pm UTC](https://discuss.elastic.co/t/cannot-use-non-grouped-column-error-how-to-get-around-this/208579/1 "2019-11-19T20:33:10Z")

</div>

I'm running the following SQL command as shown below. This command looks at all row between the respective start and date and find the MAX of the TotalCPULoadMIPS field for each day.  
If i remove the "@timestamp" the SQL runs properly. The command will not accept more than one field. It only accepts one field as long as we use an arithmetic function.

```auto
POST /_sql?format=csv
{"query":"SELECT \"@timestamp\", MAX(TotalCPULoadMIPS) AS PeakMips FROM testwebsphere WHERE \"@timestamp\" BETWEEN CAST('2019-03-01' AS DATE) AND CAST('2019-04-30' AS DATE) GROUP BY CAST (\"@timestamp\" AS DATE) " }

```

The error we get is shown below:

```auto
{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem(s)\nline 1:9: Cannot use non-grouped column [@timestamp], expected [CAST (\"@timestamp\" AS DATE)]"
      }
    ],
    "type": "verification_exception",
    "reason": "Found 1 problem(s)\nline 1:9: Cannot use non-grouped column [@timestamp], expected [CAST (\"@timestamp\" AS DATE)]"
  },
  "status": 400
}

```

How can i add another field in the SELECT statement? What is the proper syntax? Why is this not accepted syntax?

Also tried:

```auto
POST /_sql?format=csv
{"query":"SELECT CAST (\"@timestamp\" AS DATE), MAX(TotalCPULoadMIPS) AS PeakMips FROM testwebsphere WHERE \"@timestamp\" BETWEEN CAST('2019-03-01' AS DATE) AND CAST('2019-04-30' AS DATE) GROUP BY CAST (\"@timestamp\" AS DATE) " }

```

Got this error:

```auto
{
  "error": {
    "root_cause": [
      {
        "type": "folding_exception",
        "reason": "line 1:9: Cannot find grouping for 'CAST (\"@timestamp\" AS DATE)'"
      }
    ],
    "type": "folding_exception",
    "reason": "line 1:9: Cannot find grouping for 'CAST (\"@timestamp\" AS DATE)'"
  },
  "status": 400
}

```

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [November 20, 2019, 8:19am UTC](https://discuss.elastic.co/t/cannot-use-non-grouped-column-error-how-to-get-around-this/208579/2 "2019-11-20T08:19:08Z")

</div>

Try this one:

`SELECT HISTOGRAM(\"@timestamp\", INTERVAL 1 DAY) AS day, MAX(TotalCPULoadMIPS) AS PeakMips FROM testwebsphere WHERE \"@timestamp\" BETWEEN '2019-03-01T00:00:00.000Z' AND '2019-04-30T00:00:00.000Z' GROUP BY day`

---

<div class="post-metadata">

**Author:** ![pgervais](https://avatars.discourse-cdn.com/v4/letter/p/65b543/32.png) [@pgervais](https://discuss.elastic.co/u/pgervais)\
**Post date:** [November 20, 2019, 12:51pm UTC](https://discuss.elastic.co/t/cannot-use-non-grouped-column-error-how-to-get-around-this/208579/3 "2019-11-20T12:51:14Z")

</div>

Andrei  
Thank you very much for this. It works like a charm!

---

<div class="post-metadata">

**Author:** ![pgervais](https://avatars.discourse-cdn.com/v4/letter/p/65b543/32.png) [@pgervais](https://discuss.elastic.co/u/pgervais)\
**Post date:** [November 20, 2019, 12:59pm UTC](https://discuss.elastic.co/t/cannot-use-non-grouped-column-error-how-to-get-around-this/208579/4 "2019-11-20T12:59:14Z")

</div>

Andrei,  
If i modify this slightly ( i removed the MAX operation) as shown below, i get no errors but get nothing back. Why?

POST /\_sql?format=csv  
{"query":"SELECT HISTOGRAM("@timestamp", INTERVAL 1 DAY) AS day, TotalCPULoadMIPS FROM testwebsphere WHERE "@timestamp" BETWEEN '2019-03-01T00:00:00.000Z' AND '2019-04-30T00:00:00.000Z' GROUP BY day" }

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [November 20, 2019, 10:16pm UTC](https://discuss.elastic.co/t/cannot-use-non-grouped-column-error-how-to-get-around-this/208579/5 "2019-11-20T22:16:43Z")

</div>

Probably because the histogram is an aggregation in itself, which means the documents are grouped somehow (in your case they are grouped by day). And when you do an aggregation, you don't get the value of a field because there is no single value: each document in the group has a value for that specific field and it doesn't make sense to get back a value for a group of documents when each document can have a different value for that field.

Instead, you need to apply an aggregating value - like MAX, MIN, AVG - which act on all values of that specific field from that group of documents grouped by the histogram.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 18, 2019, 10:17pm UTC](https://discuss.elastic.co/t/cannot-use-non-grouped-column-error-how-to-get-around-this/208579/6 "2019-12-18T22:17:00Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.