# Get aggregation value and source field in one st

**URL:** <https://discuss.elastic.co/t/get-aggregation-value-and-source-field-in-one-st/120162>\
**Category:** Elasticsearch\
**Created:** [February 16, 2018, 12:16pm UTC](https://discuss.elastic.co/t/get-aggregation-value-and-source-field-in-one-st/120162 "2018-02-16T12:16:17Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![devil\_srj7](https://avatars.discourse-cdn.com/v4/letter/d/8edcca/32.png) [@devil\_srj7](https://discuss.elastic.co/u/devil_srj7)\
**Post date:** [February 16, 2018, 12:16pm UTC](https://discuss.elastic.co/t/get-aggregation-value-and-source-field-in-one-st/120162/1 "2018-02-16T12:16:17Z")

</div>

My Index has data as fields as

1. dept\_id
2. dept\_name
3. dept\_manager
4. year
5. month
6. expenditure

there are multiple documents of each dept\_id making expenditure corresponding to year and month.  
I need to to get the total expenditure made by all dept in so and so year and month.

I can achieve this by :

```
GET Index/_search?size=0
{

  "query": {
    "bool": {
      "must": [
        {"match": { "year": 2017 } },
        {"match": { "month": 2 }         
      ]
    }
  },
  "aggs": {
    "By Dept": {
      "terms": {
        "field": "dept_id",
        "size": 50
      },
      "aggs": {
        "total Expenditure": {
          "sum": {
            "field": "expenditure"
          }
        }
      }
    }
 }

```

but this only gives me the total sum for each dept.

I require the following.

**dept\_id , dept\_name, dept\_manager, total expenditure made by the dept in month=3 and year=2017** in one single set

---

<div class="post-metadata">

**Author:** ![Ayush\_Mathur](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ayush_mathur/32/77134_2.png) [@Ayush\_Mathur](https://discuss.elastic.co/u/Ayush_Mathur)\
**Post date:** [February 16, 2018, 4:54pm UTC](https://discuss.elastic.co/t/get-aggregation-value-and-source-field-in-one-st/120162/2 "2018-02-16T16:54:51Z")

</div>

I believe what you need is nested aggregation in this case:

```
"aggs": {
    "DeptID": {
      "terms": {
        "field": "dept_id",
        "size": 50
      },
      "aggs": {
         "DeptName" : {
            "terms" : {
                "field" : "dept_name.keyword",
                "size" : 50
            },
           "aggs" : {
               "DeptMngr" : {
                   "terms" : {
                         "field" : "dept_manager.keyword",
                         "size" : 50
                  },
                  "aggs": {
                       "total Expenditure": {
                            "sum": {
                                 "field": "expenditure"
                            }
                        }
                   }
              }
           }
        }
     }
```

---

<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 16, 2018, 4:55pm UTC](https://discuss.elastic.co/t/get-aggregation-value-and-source-field-in-one-st/120162/3 "2018-03-16T16:55:04Z")

</div>

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