X-pack sql - summation of case statement does not work

[isurika_lakmali (isurika)]

Hi Team,

I am trying to execute following sql query using x-pack sql.
POST /_sql
{
"query" :
"select sum(case when metric_name ='requirement' then 1 else 0 end) as req_count ,sum(case when metric_name ='defect' then 1 else 0 end) as defect_count from my_metric"
}

But It throws following error.
{
"error": {
"root_cause": [
{
"type": "sql_illegal_argument_exception",
"reason": "Does not know how to convert argument Case for function Sum"
}
],
"type": "sql_illegal_argument_exception",
"reason": "Does not know how to convert argument Case for function Sum"
},
"status": 500
}

Appreciate if you can provide solution to overcome this error.

[Andrei_Stefan]

@isurika_lakmali this type of query (an aggregation over a function) is not currently supported. There is a more general issue reported here: https://github.com/elastic/elasticsearch/issues/36865