# Elasticsearch SQL - Error when using CASE WHEN with SUM aggregate
**URL:** https://discuss.elastic.co/t/elasticsearch-sql-error-when-using-case-when-with-sum-aggregate/231000
**Category:** Elasticsearch
**Tags:** elastic-stack-sql
**Created:** [May 4, 2020, 4:46pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-error-when-using-case-when-with-sum-aggregate/231000 "2020-05-04T16:46:34Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Pramod\_Kumar1](https://discuss.elastic.co/u/Pramod_Kumar1)
#### Post date: [May 4, 2020, 4:46pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-error-when-using-case-when-with-sum-aggregate/231000/1 "2020-05-04T16:46:34Z")
Hi Team,
I am getting an error in elasticsearch sql(in-built elasticsql within x-pack) query when using **CASE WHEN** with **SUM aggregate function**.
`{ "type": "sql_illegal_argument_exception", "reason": "Does not know how to convert argument ScalarFunctionAttribute[V2,DOUBLE,X,UNKNOWN,122824,false,123043,InternalSqlScriptUtils.caseFunction([InternalSqlScriptUtils.and(InternalSqlSc riptUtils.gte(InternalSqlScriptUtils.docValue(doc,params.v0), params.v1), InternalSqlScriptUtils.lte(InternalSqlScriptUtils.docValue(doc,params.v2), params.v3)),params.a0,params.v4]), null,CasePipe[Case[[IfConditional[Range[monthSerial{f}#122963,201704,true,201706,true]#123005,Sum[valueUsd{f}#1 22994]#123040 ], 0]]#123043] |_BinaryLogicPipe[Range[monthSerial{f}#122963,201704,true,201706,true]#123005,AND] | |_BinaryComparisonPipe[Range[monthSerial{f}#122963,201704,true,201706,true]#123005,>=] | | |_AttributeInput[monthSerial{f}#122963,monthSerial{f}#122963] | | \_ConstantInput[201704,201704] | \_BinaryComparisonPipe[Range[monthSerial{f}#122963,201704,true,201706,true]#123005,<=] | |_AttributeInput[monthSerial{f}#122963,monthSerial{f}#122963] | \_ConstantInput[201706,201706] |_AggNameInput[Sum[valueUsd{f}#122994]#123040,SUM(valueUsd)] \_ConstantInput[0,0]] for function Sum[]" }`
**Below is the sample query and elastic version details -**
[http://localhost:9200/\_sql?format=json](http://localhost:9200/_sql?format=json)
{
"query": "SELECT Id, SUM(S1) AS PreviousTotalRecords, SUM(S2) AS CurrentTotalRecords, SUM(V1) AS PreviousTotalPrice, SUM(V2) AS CurrentTotalPrice FROM (SELECT productId AS Id, (CASE WHEN monthSerial BETWEEN 201701 AND 201703 THEN COUNT(1) ELSE 0 END) S1, (CASE WHEN monthSerial BETWEEN 201701 AND 201703 THEN SUM(priceValue) ELSE 0 END) V1,(CASE WHEN monthSerial BETWEEN 201704 AND 201706 THEN COUNT(1) ELSE 0 END) S2, (CASE WHEN monthSerial BETWEEN 201704 AND 201706 THEN SUM(priceValue) ELSE 0 END) V2 FROM "my-index" WHERE monthSerial IN (201701,201702,201703,201704,201705,201706) GROUP BY productId,monthSerial) X GROUP BY Id"
}
**Elastic version - 7.3.0**
Can anyone help me out so that I can get aggregated data for comparison of two periods.
---
### Author:  [@matriv](https://discuss.elastic.co/u/matriv)
#### Post date: [May 4, 2020, 6:47pm UTC](https://discuss.elastic.co/t/elasticsearch-sql-error-when-using-case-when-with-sum-aggregate/231000/2 "2020-05-04T18:47:20Z")
First of all ES SQL has [limited support for subqueries](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html).
Secondly, the support for scalar functions (including conditionals such as the CASE expression [has been added recently](https://github.com/elastic/elasticsearch/pull/55241) and will be available with 7.8 version.)
---
### Author:  [@Pramod\_Kumar1](https://discuss.elastic.co/u/Pramod_Kumar1)
#### Post date: [May 5, 2020, 5:21am UTC](https://discuss.elastic.co/t/elasticsearch-sql-error-when-using-case-when-with-sum-aggregate/231000/3 "2020-05-05T05:21:42Z")
Hi Matriv,
Thanks for your reply. As I am using version 7.3.0, is it possible to get aggregate count in regular query? If that will be possible then I can change sub-query to regular query to get required result.
Thanks,
---
### Author:  [@matriv](https://discuss.elastic.co/u/matriv)
#### Post date: [May 5, 2020, 9:08am UTC](https://discuss.elastic.co/t/elasticsearch-sql-error-when-using-case-when-with-sum-aggregate/231000/4 "2020-05-05T09:08:44Z")
Unfortunately, I don't see a workaround for your case.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [June 2, 2020, 9:08am UTC](https://discuss.elastic.co/t/elasticsearch-sql-error-when-using-case-when-with-sum-aggregate/231000/5 "2020-06-02T09:08:45Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.