Group by not working with other column

Elastic Stack Elasticsearch
1

Hi Support,
Below query is not working for me.

POST _sql/format=txt
{
"query":"""SELECT count(*) as matched,col1,col2 FROM index WHERE col0 = 1 group by col1"""
}

Error :

{
"error": {
"root_cause": [
{
"type": "verification_exception",
"reason": "Found 1 problem(s)\nline 1:48: Unknown column [col2]"
}
],
"type": "verification_exception",
"reason": "Found 1 problem(s)\nline 1:48: Unknown column [col2]"
},
"status": 400
}

Above query is working in SphinxSql and i want use same query and get same result.

Is there any way to get result by using above query?

Can you help me on this issue?

Thanks

2

@KrishK I think the error message is pretty descriptive: you don't have a column called col2 in your index named index.

Assuming that the column name was just a typo, SELECTing multiple columns and GROUPing BY only one of them is not standard SQL and, as expected, will not work with ES SQL either. You need to have group by col1, col2.

3

Thanks

is there any way to select multiple column and grouping by with any single column?

Thanks

4

No, there isn't. The SELECT list must contain only aggregation functions (COUNT, SUM, AVG, etc.) and columns that are also in the GROUP BY.

5

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