# SUM top hit aggegration?

**URL:** <https://discuss.elastic.co/t/sum-top-hit-aggegration/154383>\
**Category:** Elasticsearch\
**Created:** [October 29, 2018, 6:26am UTC](https://discuss.elastic.co/t/sum-top-hit-aggegration/154383 "2018-10-29T06:26:14Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sjaak01](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Sjaak01](https://discuss.elastic.co/u/Sjaak01)\
**Post date:** [October 29, 2018, 6:26am UTC](https://discuss.elastic.co/t/sum-top-hit-aggegration/154383/1 "2018-10-29T06:26:14Z")

</div>

Hi,

I was wondering whether it is possible to create a scripted field that can query the top hit (last value) for each unique value of `product` and then sum that so it can be used for graphing in Kibana?

Basically I have a range of products and some companies that spend X amount of money on various products. The total amount spent on each product is delivered in .csv format every day, hence I need to find the latest value first.

The idea is that if I can sum up the last values for each product in a single value, I can then apply a term (company name) and data histrogram (per month) to get the total cost for each company per month and plot that out to easily spot trends.

---

<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:** [October 29, 2018, 11:20am UTC](https://discuss.elastic.co/t/sum-top-hit-aggegration/154383/2 "2018-10-29T11:20:32Z")

</div>

I don't believe you can run any aggregation on the source of a top hit. Fetching the top hits happens after the aggregation phase has finished so there's no chance to do additional work on them other than sorting and displaying.

Often, you can work around this. In your case, you can aggregate companies -\> products, then sort each product by it's timestamp descending. This will put the largest (most recent) timestamp at the top. If the products `terms` size is set to 1, you will only have the most recent product in that bucket, and can use that for further analytics.

It's a little less convenient and takes some fiddling but is often doable

---

<div class="post-metadata">

**Author:** ![gbrown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gbrown/32/34482_2.png) [@gbrown](https://discuss.elastic.co/u/gbrown)\
**Post date:** [October 29, 2018, 3:58pm UTC](https://discuss.elastic.co/t/sum-top-hit-aggegration/154383/3 "2018-10-29T15:58:40Z")

</div>

This question sounds very similar to [one I recently answered](https://discuss.elastic.co/t/how-to-sum-the-results-of-multiple-buckets/153438) - the answer there about how to solve this using scripted aggregations may be useful to you, although it sounds like you may have to add a date histogram aggregation into the mix as well.

---

<div class="post-metadata">

**Author:** ![Sjaak01](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Sjaak01](https://discuss.elastic.co/u/Sjaak01)\
**Post date:** [October 30, 2018, 6:31am UTC](https://discuss.elastic.co/t/sum-top-hit-aggegration/154383/4 "2018-10-30T06:31:05Z")

</div>

Thanks, I'll have a look at that and see if I can get it working.

For now I've done a quick work around by rotating my logs monthly and using the document\_id to overwrite old data.

---

<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:** [November 27, 2018, 6:31am UTC](https://discuss.elastic.co/t/sum-top-hit-aggegration/154383/5 "2018-11-27T06:31:20Z")

</div>

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