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

[pgervais (Peter Gervais)]

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.

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:

{
  "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:

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:

{
  "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
}

[Andrei_Stefan]

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

[pgervais (Peter Gervais)]

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

[pgervais (Peter Gervais)]

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" }

[Andrei_Stefan]

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.