# How to aggregate values by term to make an avarage of the result set?

**URL:** <https://discuss.elastic.co/t/how-to-aggregate-values-by-term-to-make-an-avarage-of-the-result-set/176487>\
**Category:** Kibana\
**Created:** [April 11, 2019, 7:48pm UTC](https://discuss.elastic.co/t/how-to-aggregate-values-by-term-to-make-an-avarage-of-the-result-set/176487 "2019-04-11T19:48:01Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![allanforms](https://avatars.discourse-cdn.com/v4/letter/a/f6c823/32.png) [@allanforms](https://discuss.elastic.co/u/allanforms)\
**Post date:** [April 11, 2019, 7:48pm UTC](https://discuss.elastic.co/t/how-to-aggregate-values-by-term-to-make-an-avarage-of-the-result-set/176487/1 "2019-04-11T19:48:02Z")

</div>

I would like to make an aggregation (or "sql group by" like action) to a index, so the result can be used to make a line chart of the average value of that result set, like this  
-Index data:  
|attendance id|clerk id|hours spent|year |  
|1 |A |1.5 |2015|  
|1 |B |2 |2015|  
|2 |B |3 |2015|  
|3 |C |6 |2016|  
|3 |B |3.2 |2016|  
|4 |A |7 |2017|

The line graph, when plotted, would have 4 dots with the years and man-hour values:  
2015: 3.25 [(3.5+3)/2]  
2016: 9.2 [(9)/1]  
2017: 7 [(7)/1]

Is there a way to accomplish this?

---

<div class="post-metadata">

**Author:** ![Joe\_Fleming](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joe_fleming/32/3561_2.png) [@Joe\_Fleming](https://discuss.elastic.co/u/Joe_Fleming)\
**Post date:** [April 12, 2019, 6:17pm UTC](https://discuss.elastic.co/t/how-to-aggregate-values-by-term-to-make-an-avarage-of-the-result-set/176487/2 "2019-04-12T18:17:07Z")

</div>

Yes, in Visualize:

- Set your metric to average of `hours spent`
- Add a bucket that's a Terms Agg on `clerk id`
- Add a bucket that's a Terms Agg on `year` (unless year is a date field, then use Date Histogram)

That will show you the average hours per clerk, per year, which I think is what you are asking for here.

---

<div class="post-metadata">

**Author:** ![allanforms](https://avatars.discourse-cdn.com/v4/letter/a/f6c823/32.png) [@allanforms](https://discuss.elastic.co/u/allanforms)\
**Post date:** [April 12, 2019, 6:33pm UTC](https://discuss.elastic.co/t/how-to-aggregate-values-by-term-to-make-an-avarage-of-the-result-set/176487/3 "2019-04-12T18:33:18Z")

</div>

Hi @Joe_Fleming, thank you for your answer.  
In fact I'd like to have an avarage hour per year, not year and clerk.  
I could create a new index with the agretation already done by attendence id, but I'm trying to find out if there is another way of doing it.

---

<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:** [May 10, 2019, 6:33pm UTC](https://discuss.elastic.co/t/how-to-aggregate-values-by-term-to-make-an-avarage-of-the-result-set/176487/4 "2019-05-10T18:33:19Z")

</div>

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