# Sum of duration field of max per group in Elasticsearch

**URL:** <https://discuss.elastic.co/t/sum-of-duration-field-of-max-per-group-in-elasticsearch/342285>\
**Category:** Elasticsearch\
**Created:** [September 4, 2023, 8:36pm UTC](https://discuss.elastic.co/t/sum-of-duration-field-of-max-per-group-in-elasticsearch/342285 "2023-09-04T20:36:41Z")\
**Posts on this page:** 4\
**Page:** 2

<div class="post-metadata">

**Author:** ![carollyl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/carollyl/32/120726_2.png) [@carollyl](https://discuss.elastic.co/u/carollyl)\
**Post date:** [September 11, 2023, 7:08am UTC](https://discuss.elastic.co/t/sum-of-duration-field-of-max-per-group-in-elasticsearch/342285/21 "2023-09-11T07:08:53Z")

</div>

Hi @stephenb

I don't see `Rank by Custom` option when attempting to add row. Is it due to v7.17.0? In this case, is there any alternative?

I'm still confused, where should this query be used (besides verifying the results on Dev Tools) to create the `table` visualizer.

```auto
GET discuss-duration/_search
{
  "size": 0,
  "aggs": {
    "workflow": {
      "terms": {
        "field": "workflow"
      },
      "aggs": {
        "ids": {
          "terms": {
            "field": "id",
            "order": { "max_id": "desc" },
            "size": 1
          },
          "aggs": {
            "max_id": {
              "max": {
                "field": "id"
              }
            },
            "sum_duration": {
              "sum": {
                "field": "duration"
              }
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [September 11, 2023, 2:08pm UTC](https://discuss.elastic.co/t/sum-of-duration-field-of-max-per-group-in-elasticsearch/342285/22 "2023-09-11T14:08:00Z")

</div>

Hi @carollyl

> [@carollyl](#):
>
> I don't see `Rank by Custom` option when attempting to add row. Is it due to v7.17.0? In this case, is there any alternative?

That functionality must have come out in the 8.x versions at some point. I do not think there is an alternative to that and no, it won't be added. 7.17 is only getting minor bug fix patches and security patches.

Perhaps there's some confusion.

Visualizations do not work off a custom DSL query they can only be built through the visualization.

If you want that exact visualization, I think you're going to have to use something like Vega but again I do not have experience with that and I can't help.

If you take the query I showed you and perhaps open a topic with help me build a Vega visualization. Perhaps someone else can help.

Another possibility is to look at the transform functionality and see if you can get the data you want and do that form and then build a more simple visualization on top of it.

---

<div class="post-metadata">

**Author:** ![carollyl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/carollyl/32/120726_2.png) [@carollyl](https://discuss.elastic.co/u/carollyl)\
**Post date:** [September 16, 2023, 6:31am UTC](https://discuss.elastic.co/t/sum-of-duration-field-of-max-per-group-in-elasticsearch/342285/23 "2023-09-16T06:31:01Z")

</div>

Hi @stephenb , Thank you so much for your help on the query. I managed to use Vega creating bar chart for the duration per workflow based on max id.

---

<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 14, 2023, 6:31am UTC](https://discuss.elastic.co/t/sum-of-duration-field-of-max-per-group-in-elasticsearch/342285/24 "2023-10-14T06:31:55Z")

</div>

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

[Previous page](https://discuss.elastic.co/t/sum-of-duration-field-of-max-per-group-in-elasticsearch/342285.md?page=1)