# Aggregation of two fields in single query

**URL:** <https://discuss.elastic.co/t/aggregation-of-two-fields-in-single-query/200803>\
**Category:** Elasticsearch\
**Created:** [September 24, 2019, 7:02am UTC](https://discuss.elastic.co/t/aggregation-of-two-fields-in-single-query/200803 "2019-09-24T07:02:48Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![rgranit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rgranit/32/42186_2.png) [@rgranit](https://discuss.elastic.co/u/rgranit)\
**Post date:** [September 24, 2019, 7:02am UTC](https://discuss.elastic.co/t/aggregation-of-two-fields-in-single-query/200803/1 "2019-09-24T07:02:48Z")

</div>

Hello,

I have aמ index with documents that contain several fields, among these are:

grade  
person\_id

I wish to create an aggregation that count the number of documents found for each person\_id and for each person sum or average the grade of the documents associated with it.

Any suggestions how to conduct this aggregation?

Thanks a lot!

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [September 24, 2019, 8:36am UTC](https://discuss.elastic.co/t/aggregation-of-two-fields-in-single-query/200803/2 "2019-09-24T08:36:07Z")

</div>

Hey,

so you need an aggregation within an aggregation. It seems to me, that you first want to group by `person_id`, which means, you need a `terms` aggregation on that field. Within that aggregation you need an `avg` or `sum` aggregation on the `grade` field - and that should be it.

Would that work as a start or am I missing something in the requirements?

--Alex

---

<div class="post-metadata">

**Author:** ![rgranit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rgranit/32/42186_2.png) [@rgranit](https://discuss.elastic.co/u/rgranit)\
**Post date:** [September 24, 2019, 12:41pm UTC](https://discuss.elastic.co/t/aggregation-of-two-fields-in-single-query/200803/3 "2019-09-24T12:41:47Z")

</div>

Thanks @spinscale yes, this is what I have in mind right now:

```
  "aggs": {
    "group_by_res": {
      "terms": {
        "field": "person_id"
      },
      "aggs": {
        "average_grade": {
          "sum": {
            "field": "grade"
          }
        }
      }
    }
  } 

```

What do you think?

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [September 24, 2019, 12:57pm UTC](https://discuss.elastic.co/t/aggregation-of-two-fields-in-single-query/200803/4 "2019-09-24T12:57:39Z")

</div>

looks good to me. However this only gives you the top10 results, you may want to look at the [composite aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/7.3/search-aggregations-bucket-composite-aggregation.html) for to retrieve all the buckets.

---

<div class="post-metadata">

**Author:** ![rgranit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rgranit/32/42186_2.png) [@rgranit](https://discuss.elastic.co/u/rgranit)\
**Post date:** [September 26, 2019, 5:35am UTC](https://discuss.elastic.co/t/aggregation-of-two-fields-in-single-query/200803/5 "2019-09-26T05:35:44Z")

</div>

can't I just change the size of the agg to increase the number of results?

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [September 26, 2019, 7:01am UTC](https://discuss.elastic.co/t/aggregation-of-two-fields-in-single-query/200803/6 "2019-09-26T07:01:21Z")

</div>

yes, but there is a limit to the maximum number of buckets, where as the composite agg allows for pagination.

---

<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:** [October 24, 2019, 7:01am UTC](https://discuss.elastic.co/t/aggregation-of-two-fields-in-single-query/200803/7 "2019-10-24T07:01:27Z")

</div>

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