# ElasticSearch aggregation shows unexpected result for SUM **URL:** \ **Category:** Elasticsearch\ **Tags:** painless\ **Created:** [September 3, 2021, 1:42pm UTC](https://discuss.elastic.co/t/elasticsearch-aggregation-shows-unexpected-result-for-sum/283286 "2021-09-03T13:42:11Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![VaZa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vaza/32/84215_2.png) [@VaZa](https://discuss.elastic.co/u/VaZa)\ **Post date:** [September 3, 2021, 1:42pm UTC](https://discuss.elastic.co/t/elasticsearch-aggregation-shows-unexpected-result-for-sum/283286/1 "2021-09-03T13:42:11Z")
Trying to apply sum aggregation and get unexpected result **1. prepare dataset** ```auto $cat products.json {"index":{"_id":"1"}} {"productId": 10,"shopId": 45,"prices": {"retailPrice": 525000000.02,"sumRetailPrice": 5250000000.2},"count": 10} {"index":{"_id":"2"}} {"productId": 10,"shopId": 48,"prices": {"retailPrice": 26250000004,"sumRetailPrice": 5250000000.8},"count": 20} ``` **2. bulk insert** ```auto curl -XPOST localhost:9200/25products/_bulk -H "Content-Type: application/x-ndjson" --data-binary @./products.json ``` **3. view mapping** ```auto curl -XGET "http://localhost:9200/25products/_mapping?pretty" { "25products" : { "mappings" : { "properties" : { "count" : { "type" : "long" }, "prices" : { "properties" : { "retailPrice" : { "type" : "float" }, "sumRetailPrice" : { "type" : "float" } } }, "productId" : { "type" : "long" }, "shopId" : { "type" : "long" } } } } } ``` **4. Sum field "prices.sumRetailPrice" in Painless** ```auto curl --location --request POST 'http://localhost:9200/25products/_search?pretty' \ --header 'Content-Type: application/json' \ --data-raw '{ "aggs": {"sumSupplyPrice": { "sum": {"script": { "source": "(!doc.containsKey('\''prices.sumRetailPrice'\'') ? 0 : (doc['\''prices.sumRetailPrice'\''].size() == 0 ? 0: doc['\''prices.sumRetailPrice'\''].value))" }} }}, "query": {"bool": { "filter": [ {"terms": {"shopId": [45]}}, {"terms": {"productId": [10]}} ] }}, "from": 0, "size": 10 }' ``` result is ```auto { "took" : 2, "timed_out" : false, "_shards" : { "total" : 1, "successful" : 1, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 0.0, "hits" : [ { "_index" : "25products", "_type" : "_doc", "_id" : "1", "_score" : 0.0, "_source" : { "productId" : 10, "shopId" : 45, "prices" : { "retailPrice" : 5.2500000002E8, "sumRetailPrice" : 5.2500000002E9 }, "count" : 10 } } ] }, "aggregations" : { "sumSupplyPrice" : { "value" : 5.249999872E9 } } } ``` **4. Expectation** as well as I have a single record, expecting to have the same value as _sumRetailPrice_ ```auto "aggregations" : { "sumSupplyPrice" : { "value" : **5.2500000002E9** } } ``` But, actual result is not as expected. ```auto "aggregations" : { "sumSupplyPrice" : { "value" : **5.249999872E9** } } ``` Where am I wrong? Thanks! ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [September 3, 2021, 2:10pm UTC](https://discuss.elastic.co/t/elasticsearch-aggregation-shows-unexpected-result-for-sum/283286/2 "2021-09-03T14:10:34Z")
Hi @VaZa Welcome to the community. As an experiment try creating the mapping first with type `double` and check the results ---
**Author:** ![VaZa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vaza/32/84215_2.png) [@VaZa](https://discuss.elastic.co/u/VaZa)\ **Post date:** [September 3, 2021, 2:29pm UTC](https://discuss.elastic.co/t/elasticsearch-aggregation-shows-unexpected-result-for-sum/283286/3 "2021-09-03T14:29:46Z")
Hi @stephenb , thanks for welcoming! let me try it **1. index create** ```auto ❯ curl -XPUT 'http://localhost:9200/25products/' {"acknowledged":true,"shards_acknowledged":true,"index":"25products"} ``` **2. set mapping** ```auto curl -XPUT 'http://localhost:9200/25products/_mapping' -H 'Content-Type: application/json' -d ' { "properties": { "count": {"type": "long"}, "prices": {"properties":{ "retailPrice": {"type": "double"}, "sumRetailPrice": {"type": "double"} } }, "productId": {"type": "long"}, "shopId": {"type": "long"} } } ' {"acknowledged":true} ``` **3. insert data** ```auto curl -XPOST localhost:9200/25products/_bulk -H "Content-Type: application/x-ndjson" --data-binary @./products.json ``` 1. make aggs ```auto curl --location --request POST 'http://localhost:9200/25products/_search?pretty' \ --header 'Content-Type: application/json' \ --data-raw '{ "aggs": {"sumSupplyPrice": { "sum": {"script": { "source": "(!doc.containsKey('\''prices.sumRetailPrice'\'') ? 0 : (doc['\''prices.sumRetailPrice'\''].size() == 0 ? 0: doc['\''prices.sumRetailPrice'\''].value))" }} }}, "query": {"bool": { "filter": [ {"terms": {"shopId": [45]}}, {"terms": {"productId": [10]}} ] }}, "from": 0, "size": 10 }' ``` 5 .. **and it works..**.as expected ```auto "aggregations" : { "sumSupplyPrice" : { "value" : 5.2500000002E9 } } ``` Thanks, Stephan Well... I assume it works when we know our dataset, but when not? my .dotnet code creates indexes on demand, when new shops appear, and each of them might have different field names and values for monetary figgures. Is there any option to change field type from "float" to "double" without index re-creation? ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [September 3, 2021, 3:05pm UTC](https://discuss.elastic.co/t/elasticsearch-aggregation-shows-unexpected-result-for-sum/283286/4 "2021-09-03T15:05:46Z")
Good. No Mapping can not be changed after create BUT there is a solution. You create and index Template and Define Dynamic Mappings that will create the right types for new fields based on the name etc . > **[Mapping | Elasticsearch Guide \[7.14\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping.html#mapping-dynamic)** > **[Dynamic templates | Elasticsearch Guide \[7.14\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/dynamic-templates.html#dynamic-templates)** Your mapping would be something like this... you can also you a conditions to match only certain field name patters... take a look... not the trivial concept but may be what you need ```auto PUT my-index-000001 { "mappings": { "dynamic_templates": [ { "my-doubles": { "match_mapping_type": "float", "mapping": { "type": "double" } } }, ``` ---
**Author:** ![VaZa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vaza/32/84215_2.png) [@VaZa](https://discuss.elastic.co/u/VaZa)\ **Post date:** [September 3, 2021, 4:39pm UTC](https://discuss.elastic.co/t/elasticsearch-aggregation-shows-unexpected-result-for-sum/283286/5 "2021-09-03T16:39:11Z")
wow. I need to digest this a bit 🙂 thanks for pointing direction, @stephenb I'll come back with results ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 1, 2021, 4:39pm UTC](https://discuss.elastic.co/t/elasticsearch-aggregation-shows-unexpected-result-for-sum/283286/6 "2021-10-01T16:39:47Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.