# ES SQL :: Syntax for "sum( Value1 \* Value2)"? **URL:** \ **Category:** Elasticsearch\ **Tags:** elastic-stack-sql\ **Created:** [October 23, 2019, 4:37pm UTC](https://discuss.elastic.co/t/es-sql-syntax-for-sum-value1-value2/204914 "2019-10-23T16:37:52Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![redapplesonly](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/redapplesonly/32/57700_2.png) [@redapplesonly](https://discuss.elastic.co/u/redapplesonly)\ **Post date:** [October 23, 2019, 4:37pm UTC](https://discuss.elastic.co/t/es-sql-syntax-for-sum-value1-value2/204914/1 "2019-10-23T16:37:52Z")
Hi Elasticsearch Ninja Masters, I’ve recently spun up an Elasticsearch v7.4.0 Docker container, and I’m using the “ **elasticsearch-sql-cli** ” script to run SQL-like queries off my ES instance. Which is awesome, thank you for implementing that. I have a specific syntax question, however. I need to run the below query: ``` select Value1, sum( Value2 * Value3 ) from \"myindex2019.10.09\" group by Value1; ``` Pretty simple, really. For every record in the “ **myindex2019.10.09** ” index, multiply the values of Value2 and Value3 together. Then sum up all the (Value2 \* Value3)’s together, grouped my Value1. Should look like this: ``` Value1 | sum(Value2 * Value3) -------+--------------------- 1 | 100 2 | 200 3 | ...etc... ``` But from experimentation, I can tell you that the “ **elasticsearch-sql-cli** ” script likes `Value2 * Value3` or `sum(Value2)`, but not `sum(Value2 * Value3)` The full output I see when I try this query is below. The “ **elasticsearch-sql-cli** ” looks like it throws a Java exception, but does not crash. Is there a solution here? Any suggestion would be useful. Thanks! ``` sql> select Value1, sum( Value2 * Value3 ) from \"myindex2019.10.09\"; Server error [Server encountered an error [Does not know how to convert argument Mul[] for function Sum[]]. [SqlIllegalArgumentException[Does not know how to convert argument Mul[] for function Sum[]] at org.elasticsearch.xpack.sql.planner.QueryTranslator.field(QueryTranslator.java:470) at org.elasticsearch.xpack.sql.planner.QueryTranslator$Sums.toAgg(QueryTranslator.java:854) at org.elasticsearch.xpack.sql.planner.QueryTranslator$Sums.toAgg(QueryTranslator.java:850) at org.elasticsearch.xpack.sql.planner.QueryTranslator$SingleValueAggTranslator.asAgg(QueryTranslator.java:971) at org.elasticsearch.xpack.sql.planner.QueryTranslator$AggTranslator.apply(QueryTranslator.java:961) at org.elasticsearch.xpack.sql.planner.QueryTranslator.toAgg(QueryTranslator.java:198) at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.addAggFunction(QueryFolder.java:445) at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:359) at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:204) at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$11(Node.java:196) at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:190) at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:196) at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:593) at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:589) at org.elasticsearch.xpack.sql.rule.RuleExecutor$Transformation.(RuleExecutor.java:82) at org.elasticsearch.xpack.sql.rule.RuleExecutor.executeWithInfo(RuleExecutor.java:158) at org.elasticsearch.xpack.sql.rule.RuleExecutor.execute(RuleExecutor.java:130) at org.elasticsearch.xpack.sql.planner.QueryFolder.fold(QueryFolder.java:82) at org.elasticsearch.xpack.sql.planner.Planner.foldPlan(Planner.java:38) at org.elasticsearch.xpack.sql.planner.Planner.plan(Planner.java:28) at org.elasticsearch.xpack.sql.session.SqlSession.lambda$physicalPlan$4(SqlSession.java:160) at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62) at org.elasticsearch.xpack.sql.session.SqlSession.lambda$optimizedPlan$3(SqlSession.java:156) at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62) at org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:144) at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62) at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:277) at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62) at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:70) at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64) at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$1(TransportFieldCapabilitiesAction.java:88) at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:101) at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:97) at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:70) at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64) at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:261) at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:247) at org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleResponse(TransportService.java:1110) at org.elasticsearch.transport.TransportService$DirectResponseChannel.processResponse(TransportService.java:1188) at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1168) at org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:54) at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:47) at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:30) at org.elasticsearch.action.support.single.shard.TransportSingleShardAction.lambda$asyncShardOperation$0(TransportSingleShardAction.java:110) at org.elasticsearch.action.ActionRunnable$1.doRun(ActionRunnable.java:45) at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:773) at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:830) ]] sql> ``` ---
**Author:** ![William\_Brafford](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/william_brafford/32/51559_2.png) [@William\_Brafford](https://discuss.elastic.co/u/William_Brafford)\ **Post date:** [October 23, 2019, 8:11pm UTC](https://discuss.elastic.co/t/es-sql-syntax-for-sum-value1-value2/204914/2 "2019-10-23T20:11:54Z")
Pete, Unfortunately, I think you're hitting a documented limitation of our SQL support: > **[Using aggregation functions on top of scalar functions](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html#_using_aggregation_functions_on_top_of_scalar_functions)** > > Aggregation functions like [`MIN`](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-aggs.html#sql-functions-aggs-min), [`MAX`](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-aggs.html#sql-functions-aggs-max), etc. can only be used directly on fields, and so queries like `SELECT MAX(abs(age)) FROM test` are not possible. For what it's worth, I popped the following documents into an index called `myindex`: ```json { "key": "value", "length": 2, "width": 3 } { "key": "value", "length": 5, "width": 7 } ``` And used the Elasticsearch query DSL to get the sum of areas using the following query: ```auto GET myindex/_search { "query": { "match_all": {} }, "aggs": { "keys": { "terms": { "field": "key.keyword" }, "aggs": { "areas": { "sum": { "script": "doc['length'].value * doc['width'].value" } } } } }, "size": 0 } ``` Result: ```auto { [...], "aggregations" : { "keys" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : "value", "doc_count" : 2, "areas" : { "value" : 41.0 } } ] } } } ``` I wish I had been able to find a SQL solution for you. -William ---
**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\ **Post date:** [October 24, 2019, 8:36am UTC](https://discuss.elastic.co/t/es-sql-syntax-for-sum-value1-value2/204914/3 "2019-10-24T08:36:01Z")
Indeed, this is not supported at the moment. There is a github issue created for something very similar: [https://github.com/elastic/elasticsearch/issues/37271](https://github.com/elastic/elasticsearch/issues/37271) ---
**Author:** ![redapplesonly](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/redapplesonly/32/57700_2.png) [@redapplesonly](https://discuss.elastic.co/u/redapplesonly)\ **Post date:** [October 24, 2019, 3:11pm UTC](https://discuss.elastic.co/t/es-sql-syntax-for-sum-value1-value2/204914/4 "2019-10-24T15:11:54Z")
No problem, I appreciate the information. Sometimes its nice to know that the thing you want to do isn't possible at the moment. Much appreciated! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 21, 2019, 3:12pm UTC](https://discuss.elastic.co/t/es-sql-syntax-for-sum-value1-value2/204914/5 "2019-11-21T15:12:01Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.