# Get sum value from distinct aggregation query

**URL:** <https://discuss.elastic.co/t/get-sum-value-from-distinct-aggregation-query/93946>\
**Category:** Elasticsearch\
**Created:** [July 20, 2017, 1:20pm UTC](https://discuss.elastic.co/t/get-sum-value-from-distinct-aggregation-query/93946 "2017-07-20T13:20:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tamizh](https://avatars.discourse-cdn.com/v4/letter/t/8c91f0/32.png) [@Tamizh](https://discuss.elastic.co/u/Tamizh)\
**Post date:** [July 20, 2017, 1:20pm UTC](https://discuss.elastic.co/t/get-sum-value-from-distinct-aggregation-query/93946/1 "2017-07-20T13:20:37Z")

</div>

Actually, i am trying to implement the following MySQL query in elasticsearch.

```auto
SELECT SUM(FIELD_1) FROM (SELECT FIELD_1 FROM TABLE GROUP BY FIELD_2) AS UNIQUE_TABLE

```

I tried the following query in elasticsearch

```auto
GET index/_search
{
  "size": 0,
  "query": {}, 
  "aggs": {
    "unique_twitter": {
      "terms": {
        "size": 1000, 
        "field": "field_2"
      },
      "aggs": {
        "max": {
          "max": {
            "field": "field_1"
          }
        }
      }
    },
    "count": {
      "sum_bucket": {
        "buckets_path": "unique_twitter>max"
      }
    }
  }
}

```

The problem with the following query, I have to give the size of the first aggregation to make it work. I want it to work without specifying the size of the aggregation.  
Thanks in advance !!

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [July 20, 2017, 8:07pm UTC](https://discuss.elastic.co/t/get-sum-value-from-distinct-aggregation-query/93946/2 "2017-07-20T20:07:39Z")

</div>

That's the only way I can think to do it. You can either set a very large value for the size of the terms agg (to guarantee you catch all values), or run a cardinality aggregation first to get the size of `field_2` and then use that.

---

<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:** [August 17, 2017, 8:07pm UTC](https://discuss.elastic.co/t/get-sum-value-from-distinct-aggregation-query/93946/3 "2017-08-17T20:07:57Z")

</div>

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