SQL Query with CASE When condition and SUM

Hi Experts,

Can we use the case when with SUM in one SQL in Elastic Search7.5.1? Does ES support this in SQL or other way? I tried to do this , while there have following issue:
sql> select sum(case when model_qi_category=0 then 1 else 0 end) FROM es_voice_model_result where tenant_id = 2 group by model_qi_category;
Server error [Server encountered an error [Does not know how to convert argument Case for function Sum]. [SqlIllegalArgumentException[Does not know how to convert argument Case for function Sum]
at org.elasticsearch.xpack.sql.planner.QueryTranslator.field(QueryTranslator.java:500)
at org.elasticsearch.xpack.sql.planner.QueryTranslator$Sums.toAgg(QueryTranslator.java:884)


Hi @Zou_Justin,
That type of query is not supported at the moment. But there is a github issue for it: https://github.com/elastic/elasticsearch/issues/37271

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