# Sum aggregation is incorrect compare to scroll all docs to sum

**URL:** <https://discuss.elastic.co/t/sum-aggregation-is-incorrect-compare-to-scroll-all-docs-to-sum/213009>\
**Category:** Elasticsearch\
**Created:** [December 25, 2019, 9:24am UTC](https://discuss.elastic.co/t/sum-aggregation-is-incorrect-compare-to-scroll-all-docs-to-sum/213009 "2019-12-25T09:24:26Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![hackerwin7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hackerwin7/32/43223_2.png) [@hackerwin7](https://discuss.elastic.co/u/hackerwin7)\
**Post date:** [December 25, 2019, 9:24am UTC](https://discuss.elastic.co/t/sum-aggregation-is-incorrect-compare-to-scroll-all-docs-to-sum/213009/1 "2019-12-25T09:24:26Z")

</div>

Hi,  
I discovered that a simple sum aggregation is different to that I write a code to scroll all documents and sum the field value. the value formula is always

```auto
${sum-aggregation-result} + 1 == ${scroll-sum}

```

Elasticsearch Version: 6.3.1  
index shard num: 1  
index replica : 1  
index Mapping is like:

```auto
"filed": {
 "type": "long"
}

```

## use sum aggregation

```auto
GET .../index/_search?size=0&pretty
{
    "aggs" : {
        "sum_field_price" : { 
            "sum" : { "field" : "field_price" } 
        }
    }
}
'

```

returns the result is `1.7458313843517748E16`  
that is

```auto
17458313843517748

```

## use scroll

I use script to scroll all documents and sum the field value in script and returns

```auto
17458313843517749

```

and we also have this index data in hive, we sum the data in hive the result is also `17458313843517749`

so why the result of sum aggregation on elasticsearch is incorrect?

---

<div class="post-metadata">

**Author:** ![hackerwin7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hackerwin7/32/43223_2.png) [@hackerwin7](https://discuss.elastic.co/u/hackerwin7)\
**Post date:** [December 26, 2019, 10:16am UTC](https://discuss.elastic.co/t/sum-aggregation-is-incorrect-compare-to-scroll-all-docs-to-sum/213009/2 "2019-12-26T10:16:04Z")

</div>

cc @colings86

---

<div class="post-metadata">

**Author:** ![hackerwin7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hackerwin7/32/43223_2.png) [@hackerwin7](https://discuss.elastic.co/u/hackerwin7)\
**Post date:** [December 26, 2019, 10:29am UTC](https://discuss.elastic.co/t/sum-aggregation-is-incorrect-compare-to-scroll-all-docs-to-sum/213009/3 "2019-12-26T10:29:36Z")

</div>

Maybe it is related to kahan sum algorithm which elasticsearch use in Sum Aggregation

---

<div class="post-metadata">

**Author:** ![hackerwin7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hackerwin7/32/43223_2.png) [@hackerwin7](https://discuss.elastic.co/u/hackerwin7)\
**Post date:** [December 26, 2019, 11:52am UTC](https://discuss.elastic.co/t/sum-aggregation-is-incorrect-compare-to-scroll-all-docs-to-sum/213009/4 "2019-12-26T11:52:37Z")

</div>

After viewing the source, the SumAggregator use (double) sum += (double) value, is it the reason cause the long value field precision problem?  
the `compensation` value in SumAggregator.getLeafCollector().collect() maybe lead to the precision diff 1.0 between `1.7458313843517748E16` and `17458313843517749 `

---

<div class="post-metadata">

**Author:** ![hackerwin7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hackerwin7/32/43223_2.png) [@hackerwin7](https://discuss.elastic.co/u/hackerwin7)\
**Post date:** [December 26, 2019, 12:06pm UTC](https://discuss.elastic.co/t/sum-aggregation-is-incorrect-compare-to-scroll-all-docs-to-sum/213009/5 "2019-12-26T12:06:27Z")

</div>

So, make kahan summation in sum aggregation in elasticsearch is not 100% accurate itself, even if the field type is long ?

---

<div class="post-metadata">

**Author:** ![hackerwin7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hackerwin7/32/43223_2.png) [@hackerwin7](https://discuss.elastic.co/u/hackerwin7)\
**Post date:** [December 26, 2019, 12:40pm UTC](https://discuss.elastic.co/t/sum-aggregation-is-incorrect-compare-to-scroll-all-docs-to-sum/213009/6 "2019-12-26T12:40:28Z")

</div>

1.7458313836007748E16 + 1.0 = 1.7458313836007748E16 in double

---

<div class="post-metadata">

**Author:** ![hackerwin7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hackerwin7/32/43223_2.png) [@hackerwin7](https://discuss.elastic.co/u/hackerwin7)\
**Post date:** [December 26, 2019, 12:46pm UTC](https://discuss.elastic.co/t/sum-aggregation-is-incorrect-compare-to-scroll-all-docs-to-sum/213009/7 "2019-12-26T12:46:13Z")

</div>

So, in kahan summation, if the `corrected` is very small compare to huge double `sum`  
then the `newSum = sum + corrected` will lose the precision

---

<div class="post-metadata">

**Author:** ![hackerwin7](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hackerwin7/32/43223_2.png) [@hackerwin7](https://discuss.elastic.co/u/hackerwin7)\
**Post date:** [December 26, 2019, 1:08pm UTC](https://discuss.elastic.co/t/sum-aggregation-is-incorrect-compare-to-scroll-all-docs-to-sum/213009/8 "2019-12-26T13:08:51Z")

</div>

Another question is why sum aggregation use double for all field mapping numeric type? if field type is long, we can use long type in sum aggregation, and we will not lose the precision.

---

<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:** [January 23, 2020, 1:08pm UTC](https://discuss.elastic.co/t/sum-aggregation-is-incorrect-compare-to-scroll-all-docs-to-sum/213009/9 "2020-01-23T13:08:55Z")

</div>

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