# Aggregate counting/sum query

**URL:** <https://discuss.elastic.co/t/aggregate-counting-sum-query/164951>\
**Category:** Elasticsearch\
**Created:** [January 20, 2019, 5:46pm UTC](https://discuss.elastic.co/t/aggregate-counting-sum-query/164951 "2019-01-20T17:46:52Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ninesalt](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ninesalt/32/39981_2.png) [@ninesalt](https://discuss.elastic.co/u/ninesalt)\
**Post date:** [January 20, 2019, 5:46pm UTC](https://discuss.elastic.co/t/aggregate-counting-sum-query/164951/1 "2019-01-20T17:46:53Z")

</div>

I'm trying to figure out how to sum up many different counts in an elasticsearch index. A document in the index looks like this:

```
{
  '_source': {
    'my_field': 'Robert and Alex went with Robert to play in London and then Robert went to London',
    'ner': {
      'persons': {
        'Alex': 1,
        'Robert': 3
      },
      'organizations': {},
      'dates': {},
      'locations': {
        'London': 2
      }
    }
  }
}

```

How can I sum up all the different words in `location`, `dates` and `persons` in the index? For example if another document had 2 occurrences of `Alex`, I'd get `Alex: 3, Robert: 3, ..`

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [January 22, 2019, 1:48pm UTC](https://discuss.elastic.co/t/aggregate-counting-sum-query/164951/2 "2019-01-22T13:48:44Z")

</div>

If you have a limited number of persons/organizations/dates/locations, and you know exactly what the values of those entities are going to be, then you can use [a `sum` aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-sum-aggregation.html) to sum the counts for each value individually:

```auto
GET my_index/_search
{
  "size": 0,
  "aggs": {
    "sum_alexes": {
      "sum": {
        "field": "ner.persons.Alex"
      }
    },
    "sum_roberts": {
      "sum": {
        "field": "ner.persons.Robert"
      }
    }
  }
}

```

It's probably not the case that you know all the values beforehand though? If you're dealing with a potentially large number of different values, and you do not know the values beforehand, then it's going to be a bit more work. I would suggest you restructure your documents using [nested types](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html).

For example, just taking `person`s into account, you could create your index with a mapping like this:

```auto
PUT my_index
{
  "mappings": {
    "_doc": {
      "properties": {
        "ner": {
          "properties": {
            "persons": {
              "type": "nested",
              "properties": {
                "name": {
                  "type": "keyword"
                },
                "count": {
                  "type": "integer"
                }
              }
            }
          }
        }
      }
    }
  }
}

```

Now you will need to index your data providing an array of objects that each contain the "name" and "count" fields:

```auto
PUT my_index/_doc/1
{
  "my_field": "Robert and Alex went with Robert to play in London and then Robert went to London",
  "ner": {
    "persons": [
      {
        "name": "Alex",
        "count": 1
      },
      {
        "name": "Robert",
        "count": 3
      }
    ]
  }
}

PUT my_index/_doc/2
{
  "my_field": "Alex foo bar Alex",
  "ner": {
    "persons": [
      {
        "name": "Alex",
        "count": 2
      }
    ]
  }
}

```

You can now use a [nested aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-nested-aggregation.html) in combination with a [terms aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html) and a sum aggregation to get a list of the for example 10 names with the highest total count:

```auto
GET my_index/_search
{
  "size": 0,
  "aggs": {
    "persons": {
      "nested": {
        "path": "ner.persons"
      },
      "aggs": {
        "top_names": {
          "terms": {
            "field": "ner.persons.name",
            "size": 10,
            "order": {
              "total_sum_count": "desc"
            }
          },
          "aggs": {
            "total_sum_count": {
              "sum": {
                "field": "ner.persons.count"
              }
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![ninesalt](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ninesalt/32/39981_2.png) [@ninesalt](https://discuss.elastic.co/u/ninesalt)\
**Post date:** [January 22, 2019, 3:47pm UTC](https://discuss.elastic.co/t/aggregate-counting-sum-query/164951/3 "2019-01-22T15:47:56Z")

</div>

This is great. Thank you. However I chose to go with a different approach for this. I noticed that in the Discover tab in Kibana, it shows you the most used words in a field so I changed the structure to be like this:

```
{
 persons: ['Alex', 'Alex', 'Robert']
}

```

I realize this is less efficient than you're suggestion but your suggestion is not working too well with Kibana.

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [January 22, 2019, 8:47pm UTC](https://discuss.elastic.co/t/aggregate-counting-sum-query/164951/4 "2019-01-22T20:47:20Z")

</div>

Yeah, you're right - working with nested types is not really supported by Kibana.

I don't know what you're actually doing in Kibana, but there is one thing to be aware of with your approach. Even though you have added the word `Alex` twice to that `persons` field, if you run a terms aggregation on that field, the term `Alex` will only be counted once. The terms aggregation counts documents that contain the term - not individual occurrences of the term.

---

<div class="post-metadata">

**Author:** ![ninesalt](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ninesalt/32/39981_2.png) [@ninesalt](https://discuss.elastic.co/u/ninesalt)\
**Post date:** [February 3, 2019, 4:33pm UTC](https://discuss.elastic.co/t/aggregate-counting-sum-query/164951/5 "2019-02-03T16:33:34Z")

</div>

Sorry if this is a little unrelated to my original question, but I'm trying to figure out how to aggregate documents that follow a structure similar to my last comment. I'm trying to count the individual occurrences of the words (in other words, I'm trying to do what you stated in your last sentence), similar to what Kibana does automatically:

![image](https://us1.discourse-cdn.com/elastic/original/3X/5/9/59cf9669eb185f4b2bd12586903ec11a3ce9ec61.png)

Also, I don't understand why Kibana doesn't show a `visualize` button for these fields.

---

<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:** [March 3, 2019, 4:33pm UTC](https://discuss.elastic.co/t/aggregate-counting-sum-query/164951/6 "2019-03-03T16:33:38Z")

</div>

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