# Find average aggregation of the top N buckets **URL:** \ **Category:** Elasticsearch\ **Created:** [September 30, 2022, 5:43am UTC](https://discuss.elastic.co/t/find-average-aggregation-of-the-top-n-buckets/315515 "2022-09-30T05:43:39Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![bhavya](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bhavya/32/45679_2.png) [@bhavya](https://discuss.elastic.co/u/bhavya)\ **Post date:** [September 30, 2022, 5:43am UTC](https://discuss.elastic.co/t/find-average-aggregation-of-the-top-n-buckets/315515/1 "2022-09-30T05:43:39Z")
I'm trying to determine the average value of the "count" column for the top 5 documents ordered by "timestamp" (in descending order). **Sample Index data:** ```auto {"index":{}} {"title": "foo", "count": 1, "timestamp":"2022-09-15T09:46:43.958Z"} {"index":{}} {"title": "foo", "count": 2, "timestamp":"2022-09-14T09:46:43.958Z"} {"index":{}} {"title": "foo", "count": 3, "timestamp":"2022-09-16T09:46:43.958Z"} {"index":{}} {"title": "foo", "count": 4, "timestamp":"2022-09-13T09:46:43.958Z"} {"index":{}} {"title": "foo", "count": 5, "timestamp":"2022-09-12T09:46:43.958Z"} {"index":{}} {"title": "foo", "count": 6, "timestamp":"2022-09-11T09:46:43.958Z"} {"index":{}} {"title": "foo", "count": 7, "timestamp":"2022-09-19T09:46:43.958Z"} {"index":{}} {"title": "foo", "count": 8, "timestamp":"2022-09-18T09:46:43.958Z"} {"index":{}} {"title": "foo", "count": 9, "timestamp":"2022-09-10T09:46:43.958Z"} {"index":{}} {"title": "foo", "count": 10, "timestamp":"2022-09-17T09:46:43.958Z"} {"index":{}} {"title": "bar", "count": 20, "timestamp":"2022-09-10T09:46:43.958Z"} {"index":{}} {"title": "bar", "count": 21, "timestamp":"2022-09-11T09:46:43.958Z"} {"index":{}} {"title": "bar", "count": 22, "timestamp":"2022-09-12T09:46:43.958Z"} {"index":{}} {"title": "bar", "count": 23, "timestamp":"2022-09-13T09:46:43.958Z"} {"index":{}} {"title": "bar", "count": 24, "timestamp":"2022-09-14T09:46:43.958Z"} {"index":{}} {"title": "bar", "count": 25, "timestamp":"2022-09-15T09:46:43.958Z"} {"index":{}} {"title": "bar", "count": 26, "timestamp":"2022-09-17T09:46:43.958Z"} {"index":{}} {"title": "bar", "count": 27, "timestamp":"2022-09-18T09:46:43.958Z"} ``` **Search Query (using top hits aggregation)**: ```auto { "size": 0, "aggs": { "group_by_title": { "terms": { "field": "title.keyword", "size": 10 }, "aggs": { "take-N-tracks": { "top_hits": { "sort": [ { "timestamp": { "order": "desc" } } ], "_source": { "includes": [ "count", "title" ] }, "size": 5 } } } } } } ``` **Search Result:** ```auto { "took": 32, "timed_out": false, "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 }, "hits": { "total": { "value": 18, "relation": "eq" }, "max_score": null, "hits": [] }, "aggregations": { "group_by_index": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "foo", "doc_count": 10, "take-N-tracks": { "hits": { "total": { "value": 10, "relation": "eq" }, "max_score": null, "hits": [ { "_index": "testidx", "_id": "-jKbjIMB37c_U4Ro89zA", "_score": null, "_source": { "title": "foo", "count": 7 }, "sort": [ 1663580803958 ] }, { "_index": "testidx", "_id": "-zKbjIMB37c_U4Ro89zA", "_score": null, "_source": { "title": "foo", "count": 8 }, "sort": [ 1663494403958 ] }, { "_index": "testidx", "_id": "_TKbjIMB37c_U4Ro89zA", "_score": null, "_source": { "title": "foo", "count": 10 }, "sort": [ 1663408003958 ] }, { "_index": "testidx", "_id": "9jKbjIMB37c_U4Ro89zA", "_score": null, "_source": { "title": "foo", "count": 3 }, "sort": [ 1663321603958 ] }, { "_index": "testidx", "_id": "9DKbjIMB37c_U4Ro89y7", "_score": null, "_source": { "title": "foo", "count": 1 }, "sort": [ 1663235203958 ] } ] } } }, { "key": "bar", "doc_count": 8, "take-N-tracks": { "hits": { "total": { "value": 8, "relation": "eq" }, "max_score": null, "hits": [ { "_index": "testidx", "_id": "BTKbjIMB37c_U4Ro893A", "_score": null, "_source": { "title": "bar", "count": 27 }, "sort": [ 1663494403958 ] }, { "_index": "testidx", "_id": "BDKbjIMB37c_U4Ro893A", "_score": null, "_source": { "title": "bar", "count": 26 }, "sort": [ 1663408003958 ] }, { "_index": "testidx", "_id": "AzKbjIMB37c_U4Ro893A", "_score": null, "_source": { "title": "bar", "count": 25 }, "sort": [ 1663235203958 ] }, { "_index": "testidx", "_id": "AjKbjIMB37c_U4Ro893A", "_score": null, "_source": { "title": "bar", "count": 24 }, "sort": [ 1663148803958 ] }, { "_index": "testidx", "_id": "ATKbjIMB37c_U4Ro893A", "_score": null, "_source": { "title": "bar", "count": 23 }, "sort": [ 1663062403958 ] } ] } } } ] } } } ``` **Expected Result:** Under the bucket of `foo`, the average value should come equal to 5.8 i.e (29/5) Under the bucket of `bar`, the average value should come equal to 26.6 i.e (133/5) Use of a metric aggregation with top hits aggregation is required by me. I believe there is a workaround to sort the buckets of top hits aggregation based on this [link](https://github.com/elastic/elasticsearch/issues/29580). **However, is there a method to perform an average aggregation on the response of the top hits?** ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 28, 2022, 5:43am UTC](https://discuss.elastic.co/t/find-average-aggregation-of-the-top-n-buckets/315515/2 "2022-10-28T05:43:58Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.