# Sum aggregation precision

**URL:** <https://discuss.elastic.co/t/sum-aggregation-precision/265946>\
**Category:** Elasticsearch\
**Created:** [March 2, 2021, 11:43am UTC](https://discuss.elastic.co/t/sum-aggregation-precision/265946 "2021-03-02T11:43:40Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![DawidS](https://avatars.discourse-cdn.com/v4/letter/d/cc9497/32.png) [@DawidS](https://discuss.elastic.co/u/DawidS)\
**Post date:** [March 2, 2021, 11:43am UTC](https://discuss.elastic.co/t/sum-aggregation-precision/265946/1 "2021-03-02T11:43:40Z")

</div>

Hello,

I have a problem with sum fields with 3 decimal places. The Sum of all values is zero, but elastic sum aggregations return a very small number(like -2.837623469531536E-10).  
I have created a test index and put the same values into scaled\_float and double fields.

```auto
PUT my_index
{
  "mappings": {
    "dynamic": "strict",
    "properties": {
      "price": {
        "type": "scaled_float",
        "scaling_factor": 1000.
      },
      "decimal": {
        "type": "double"
      }
    }
  }
}

```

The proper result is returned only for scripted metric aggregation, which sum BigDecimal values of the decimal field.  
Cast all values into Big decimal may cause performance problems. It is strange that scaled\_float loses precision.  
I am looking for a better solution than scripted metric aggregation, any ideas?

Best,  
David

Query (elasticsearch 7.11, ale operations performed via Kibana):

```auto
  GET my_index/_search?request_cache=false
    {
      "size": 0,
      "aggregations": {
        "sum_scaled_float": {
          "sum": {
            "field": "price"
          }
        },
        "sum_decimal": {
          "sum": {
            "field": "decimal"
          }
        },
        "scripted_metric_sum_decimal": {
          "scripted_metric": {
            "init_script": {
              "source": "state.values = []",
              "lang": "painless"
            },
            "map_script": {
              "source": "state.values.add(BigDecimal.valueOf(doc.decimal.value))",
              "lang": "painless"
            },
            "combine_script": {
              "source": "BigDecimal value = BigDecimal.ZERO; for (a in state.values) { value = value.add(a) } return value",
              "lang": "painless"
            },
            "reduce_script": {
              "source": "BigDecimal value = BigDecimal.ZERO; for (a in states) { value = value.add(a) } return value",
              "lang": "painless"
            }
          }
        },
        "scripted_metric_scaled_float": {
          "scripted_metric": {
            "init_script": {
              "source": "state.values = []",
              "lang": "painless"
            },
            "map_script": {
              "source": "state.values.add(BigDecimal.valueOf(doc.price.value))",
              "lang": "painless"
            },
            "combine_script": {
              "source": "BigDecimal value = BigDecimal.ZERO; for (a in state.values) { value = value.add(a) } return value",
              "lang": "painless"
            },
            "reduce_script": {
              "source": "BigDecimal value = BigDecimal.ZERO; for (a in states) { value = value.add(a) } return value",
              "lang": "painless"
            }
          }
        }
      }
    }

```

Response:

```auto
    {
      "took" : 0,
      "timed_out" : false,
      "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : {
          "value" : 60,
          "relation" : "eq"
        },
        "max_score" : null,
        "hits" : []
      },
      "aggregations" : {
        "sum_decimal" : {
          "value" : -2.9103830456733704E-10
        },
        "sum_scaled_float" : {
          "value" : -2.837623469531536E-10
        },
        "scripted_metric_scaled_float" : {
          "value" : 2.5E-12
        },
        "scripted_metric_sum_decimal" : {
          "value" : 0.000
        }
      }
    }

```

DATA :

```auto
    POST my_index/_doc/1
    {
    "price" : 6845.828,
    "decimal" : 6845.828
    }

    POST my_index/_doc/2
    {
    "price" : 47298.449,
    "decimal" : 47298.449
    }

    POST my_index/_doc/3
    {
    "price" : 17425.738,
    "decimal" : 17425.738
    }

    POST my_index/_doc/4
    {
    "price" : 224045.261,
    "decimal" : 224045.261
    }

    POST my_index/_doc/5
    {
    "price" : 53795.445,
    "decimal" : 53795.445
    }

    POST my_index/_doc/6
    {
    "price" : 42942.009,
    "decimal" : 42942.009
    }

    POST my_index/_doc/7
    {
    "price" : -115756.729,
    "decimal" : -115756.729
    }

    POST my_index/_doc/8
    {
    "price" : -28939.185,
    "decimal" : -28939.185
    }

    POST my_index/_doc/9
    {
    "price" : -42615.034,
    "decimal" : -42615.034
    }

    POST my_index/_doc/10
    {
    "price" : 746817.544,
    "decimal" : 746817.544
    }

    POST my_index/_doc/11
    {
    "price" : 93352.193,
    "decimal" : 93352.193
    }

    POST my_index/_doc/12
    {
    "price" : -3925718.071,
    "decimal" : -3925718.071
    }

    POST my_index/_doc/13
    {
    "price" : -981429.537,
    "decimal" : -981429.537
    }

    POST my_index/_doc/14
    {
    "price" : 10891.089,
    "decimal" : 10891.089
    }

    POST my_index/_doc/15
    {
    "price" : 410749.647,
    "decimal" : 410749.647
    }

    POST my_index/_doc/16
    {
    "price" : 10105.062,
    "decimal" : 10105.062
    }

    POST my_index/_doc/17
    {
    "price" : 2644.983,
    "decimal" : 2644.983
    }

    POST my_index/_doc/18
    {
    "price" : 660.0,
    "decimal" : 660.0
    }

    POST my_index/_doc/19
    {
    "price" : 37288.152,
    "decimal" : 37288.152
    }

    POST my_index/_doc/20
    {
    "price" : 23649.219,
    "decimal" : 23649.219
    }

    POST my_index/_doc/21
    {
    "price" : 102687.409,
    "decimal" : 102687.409
    }

    POST my_index/_doc/22
    {
    "price" : 5289.955,
    "decimal" : 5289.955
    }

    POST my_index/_doc/23
    {
    "price" : 186704.386,
    "decimal" : 186704.386
    }

    POST my_index/_doc/24
    {
    "price" : 18670.443,
    "decimal" : 18670.443
    }

    POST my_index/_doc/25
    {
    "price" : 2800.567,
    "decimal" : 2800.567
    }

    POST my_index/_doc/26
    {
    "price" : 23650.0,
    "decimal" : 23650.0
    }

    POST my_index/_doc/27
    {
    "price" : 2640.0,
    "decimal" : 2640.0
    }

    POST my_index/_doc/28
    {
    "price" : 311173.984,
    "decimal" : 311173.984
    }

    POST my_index/_doc/29
    {
    "price" : 65649.419,
    "decimal" : 65649.419
    }

    POST my_index/_doc/30
    {
    "price" : -10653.753,
    "decimal" : -10653.753
    }

    POST my_index/_doc/31
    {
    "price" : 102687.409,
    "decimal" : 102687.409
    }

    POST my_index/_doc/32
    {
    "price" : 215181.78,
    "decimal" : 215181.78
    }

    POST my_index/_doc/33
    {
    "price" : 68458.269,
    "decimal" : 68458.269
    }

    POST my_index/_doc/34
    {
    "price" : 74681.75,
    "decimal" : 74681.75
    }

    POST my_index/_doc/35
    {
    "price" : 11202.268,
    "decimal" : 11202.268
    }

    POST my_index/_doc/36
    {
    "price" : 2800.567,
    "decimal" : 2800.567
    }

    POST my_index/_doc/37
    {
    "price" : 410749.647,
    "decimal" : 410749.647
    }

    POST my_index/_doc/38
    {
    "price" : 585007.071,
    "decimal" : 585007.071
    }

    POST my_index/_doc/39
    {
    "price" : 24893.913,
    "decimal" : 24893.913
    }

    POST my_index/_doc/40
    {
    "price" : 94600.0,
    "decimal" : 94600.0
    }

    POST my_index/_doc/41
    {
    "price" : 11202.268,
    "decimal" : 11202.268
    }

    POST my_index/_doc/42
    {
    "price" : 16412.352,
    "decimal" : 16412.352
    }

    POST my_index/_doc/43
    {
    "price" : 171768.036,
    "decimal" : 171768.036
    }

    POST my_index/_doc/44
    {
    "price" : 10579.91,
    "decimal" : 10579.91
    }

    POST my_index/_doc/45
    {
    "price" : 94596.887,
    "decimal" : 94596.887
    }

    POST my_index/_doc/46
    {
    "price" : 17114.57,
    "decimal" : 17114.57
    }

    POST my_index/_doc/47
    {
    "price" : 23338.051,
    "decimal" : 23338.051
    }

    POST my_index/_doc/48
    {
    "price" : 6223.481,
    "decimal" : 6223.481
    }

    POST my_index/_doc/49
    {
    "price" : 4356.440,
    "decimal" : 4356.440
    }

    POST my_index/_doc/50
    {
    "price" : 2526.271,
    "decimal" : 2526.271
    }

    POST my_index/_doc/51
    {
    "price" : 56011.318,
    "decimal" : 56011.318
    }

    POST my_index/_doc/52
    {
    "price" : 35162.655,
    "decimal" : 35162.655
    }

    POST my_index/_doc/53
    {
    "price" : 43564.356,
    "decimal" : 43564.356
    }

    POST my_index/_doc/54
    {
    "price" : 140650.631,
    "decimal" : 140650.631
    }

    POST my_index/_doc/55
    {
    "price" : 27383.312,
    "decimal" : 27383.312
    }

    POST my_index/_doc/56
    {
    "price" : 149152.608,
    "decimal" : 149152.608
    }

    POST my_index/_doc/57
    {
    "price" : 77793.496,
    "decimal" : 77793.496
    }

    POST my_index/_doc/58
    {
    "price" : 146251.765,
    "decimal" : 146251.765
    }

    POST my_index/_doc/59
    {
    "price" : 11824.615,
    "decimal" : 11824.615
    }

    POST my_index/_doc/60
    {
    "price" : 21159.831,
    "decimal" : 21159.831
    }

```

---

<div class="post-metadata">

**Author:** ![nik9000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nik9000/32/44947_2.png) [@nik9000](https://discuss.elastic.co/u/nik9000)\
**Post date:** [March 2, 2021, 12:13pm UTC](https://discuss.elastic.co/t/sum-aggregation-precision/265946/2 "2021-03-02T12:13:33Z")

</div>

The sum aggregation uses a kahan summation on double precision floating point values. I expect loss of precision, even if you are summing scaled floats.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 30, 2021, 12:13pm UTC](https://discuss.elastic.co/t/sum-aggregation-precision/265946/3 "2021-03-30T12:13:44Z")

</div>

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