# Sum aggregation on field by distinct value of another field with high cardinality

**URL:** <https://discuss.elastic.co/t/sum-aggregation-on-field-by-distinct-value-of-another-field-with-high-cardinality/210111>\
**Category:** Elasticsearch\
**Created:** [December 2, 2019, 5:51am UTC](https://discuss.elastic.co/t/sum-aggregation-on-field-by-distinct-value-of-another-field-with-high-cardinality/210111 "2019-12-02T05:51:39Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![artur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/artur/32/58607_2.png) [@artur](https://discuss.elastic.co/u/artur)\
**Post date:** [December 2, 2019, 5:51am UTC](https://discuss.elastic.co/t/sum-aggregation-on-field-by-distinct-value-of-another-field-with-high-cardinality/210111/1 "2019-12-02T05:51:40Z")

</div>

I have a document like this:

```
{
    "_id": 1, 
    "factor": 100,
    "field_with_high_cardinality": 1000
}

```

I know that for each unique `field_with_high_cardinality` the `factor` is always the same.

I need to calculate `sum` aggregation of `factor` field considering only unique (distinct) values of `field_with_high_cardinality` .

I tried to calculate `terms` for `field_with_high_cardinality` , then `avg` for each `term` (since all `factor` fields are same, `avg` will give me the `risk` field back). Then `sum_bucket` on avarages:

```
"aggs": {
    "terms_agg": {
      "terms": {
        "field": "field_with_high_cardinality",
        "size": 1000000
      },
      "aggs": {
        "avg_risks": {
          "avg": {
            "field": "factor"
          }
        }
      }
    },
    "sums":{
      "sum_bucket": {
        "buckets_path": "terms_agg.avg_risks"
      }
    }
}

```

But since I have a lot of `field_with_high_cardinality` values (almost 99% are unique) I get 2 problems:

1. Unacceptably long execution time
2. All `risk_terms` buckets are returned as part of the response, but I need only the `sums` field.

---

<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:** [December 30, 2019, 5:58am UTC](https://discuss.elastic.co/t/sum-aggregation-on-field-by-distinct-value-of-another-field-with-high-cardinality/210111/2 "2019-12-30T05:58:22Z")

</div>

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