# Elasticsearch query to group on different parameter and max of different parameter

**URL:** <https://discuss.elastic.co/t/elasticsearch-query-to-group-on-different-parameter-and-max-of-different-parameter/55612>\
**Category:** Elasticsearch\
**Created:** [July 15, 2016, 11:13am UTC](https://discuss.elastic.co/t/elasticsearch-query-to-group-on-different-parameter-and-max-of-different-parameter/55612 "2016-07-15T11:13:37Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![siddharthgoel88](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/siddharthgoel88/32/10616_2.png) [@siddharthgoel88](https://discuss.elastic.co/u/siddharthgoel88)\
**Post date:** [July 15, 2016, 11:13am UTC](https://discuss.elastic.co/t/elasticsearch-query-to-group-on-different-parameter-and-max-of-different-parameter/55612/1 "2016-07-15T11:13:38Z")

</div>

I want to write an Elasticsearch query to group by document on an attribute (say attr1), get only the top 10 result of this group by sorted by another attibute (say attr2) and in this result of 10 documents I need to find the max of an attribute (say attr3).

In sql, I would have written the same like this -

```
select max(attr3) from 
 (select top 10 * from 
    (select sum(xyz) as attr2, count(abc) as attr3 from sometable group by attr1 )  
 order by attr2 desc); 

```

Could someone help me an analogous query in elasticsearch for it?

---

<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:** [July 15, 2016, 11:28am UTC](https://discuss.elastic.co/t/elasticsearch-query-to-group-on-different-parameter-and-max-of-different-parameter/55612/2 "2016-07-15T11:28:19Z")

</div>

Have a look at aggregations. The terms aggregation is like group by. There  
is also a max and average aggregation.

---

<div class="post-metadata">

**Author:** ![siddharthgoel88](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/siddharthgoel88/32/10616_2.png) [@siddharthgoel88](https://discuss.elastic.co/u/siddharthgoel88)\
**Post date:** [July 18, 2016, 9:29am UTC](https://discuss.elastic.co/t/elasticsearch-query-to-group-on-different-parameter-and-max-of-different-parameter/55612/3 "2016-07-18T09:29:13Z")

</div>

Thanks @nik9000 for your reply.

I found that if I perform a nested aggregation followed by pipeline aggregation then I was able to mimic the above stated SQL query. Following is the query payload I built -

```
POST /sometable/_search
{
   "from":0,
   "size":0,
   "query":{
      "match_all": {}
   },
   "aggs": {
     "group_by_attr1" : {
       "terms": {
         "field": "attr1",
         "order": {
          "attr2" : "desc" 
        },
        "size": 10
       },
       "aggs": {
         "attr2": {
           "sum": {
             "field": "xyz"
           }
         },
         "attr3" : {
           "value_count": {
             "field": "abc"
           }
         }
       }
     },
    "max_attr3": {
      "max_bucket": {
          "buckets_path": "group_by_attr1>attr3"
      }
    }
   }
} 

```

I believe it is fine here. Do you have any comments/suggestions on my query?

---

<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:** [July 5, 2017, 10:34pm UTC](https://discuss.elastic.co/t/elasticsearch-query-to-group-on-different-parameter-and-max-of-different-parameter/55612/4 "2017-07-05T22:34:47Z")

</div>