# Query to find sum of string fields

**URL:** <https://discuss.elastic.co/t/query-to-find-sum-of-string-fields/61354>\
**Category:** Elasticsearch\
**Created:** [September 23, 2016, 9:51am UTC](https://discuss.elastic.co/t/query-to-find-sum-of-string-fields/61354 "2016-09-23T09:51:28Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sruthi](https://avatars.discourse-cdn.com/v4/letter/s/ee7513/32.png) [@sruthi](https://discuss.elastic.co/u/sruthi)\
**Post date:** [September 23, 2016, 9:51am UTC](https://discuss.elastic.co/t/query-to-find-sum-of-string-fields/61354/1 "2016-09-23T09:51:28Z")

</div>

Suppose I have records like this  
{storename:s1, product:p1, status:available}  
{storename:s1, product:p2, status:not available}  
{storename:s1, product:p3, status:available}

{storename:s2, product:p1, status:not available}

I want to write a query to get the following result  
storename:s1  
available: 2  
not available: 1

storename: s2  
available: 1  
not available: 0

Since status field is string, I am not sure how to take the sum of those fields.

---

<div class="post-metadata">

**Author:** ![danielmitterdorfer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/danielmitterdorfer/32/110510_2.png) [@danielmitterdorfer](https://discuss.elastic.co/u/danielmitterdorfer)\
**Post date:** [September 23, 2016, 1:07pm UTC](https://discuss.elastic.co/t/query-to-find-sum-of-string-fields/61354/2 "2016-09-23T13:07:17Z")

</div>

Hi @sruthi,

you have to use pipeline aggregations. This is a complete example that works with Elasticsearch 5.0:

```auto
PUT /store
{
   "mappings": {
      "product": {
         "properties": {
            "product": {
               "type": "keyword"
            },
            "status": {
               "type": "keyword"
            },
            "storename": {
               "type": "keyword"
            }
         }
      }
   }
}

PUT /store/product/1
{
    "storename": "s1",
    "product": "p1",
    "status": "available"
}

PUT /store/product/2
{
    "storename": "s1",
    "product": "p2",
    "status": "not available"
}

PUT /store/product/3
{
    "storename": "s2",
    "product": "p3",
    "status": "available"
}

GET /store/product/_search
{
   "query": {
      "match_all": {}
   },
   "size": 0,
   "aggs": {
      "stores": {
         "terms": {
            "field": "storename"
         },
         "aggs": {
            "status": {
               "terms": {
                  "field": "status"
               }
            }
         }
      }
   }
}

```

For 2.x, replace `"type": "keyword"` with:

```auto
"type": "string"
"index": "not_analyzed"

```

Daniel

---

<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:** [July 5, 2017, 10:17pm UTC](https://discuss.elastic.co/t/query-to-find-sum-of-string-fields/61354/3 "2017-07-05T22:17:45Z")

</div>