# Elasticsearch terms, group by and sum aggregation **URL:** \ **Category:** Elasticsearch\ **Created:** [November 9, 2016, 10:52am UTC](https://discuss.elastic.co/t/elasticsearch-terms-group-by-and-sum-aggregation/65466 "2016-11-09T10:52:40Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![aniketpatil](https://avatars.discourse-cdn.com/v4/letter/a/85f322/32.png) [@aniketpatil](https://discuss.elastic.co/u/aniketpatil)\ **Post date:** [November 9, 2016, 10:52am UTC](https://discuss.elastic.co/t/elasticsearch-terms-group-by-and-sum-aggregation/65466/1 "2016-11-09T10:52:40Z")
I have documents in elasticsearch that contains attributes like ``` { [ { "DTCREATED": "2016-09-19T18:30:00.000Z", "amount ": 12 "SUBMERCHANTTRANSACTION" : -1 }, { "DTCREATED": "2016-09-20T18:30:00.000Z", "amount ": 30 "SUBMERCHANTTRANSACTION" : 0 }, { "DTCREATED": "2016-10-25T18:30:00.000Z", "amount ": 35 "SUBMERCHANTTRANSACTION" : -1 } ] ``` } ('-1' for yes '0' for no) I want to get sum of all amount monthwise where SUBMERCHANTTRANSACTION is '-1' (Yes). I have tried below query ``` requestQuery = Requests.searchRequest(ConstantsValue.indexName) .types(ConstantsValue._Type) .source("{size:999999," + "\"_source\" : " + "[\"DTCREATED\", \"amount\"]" + ",\"filter\": " + "{\"terms\": {\"SUBMERCHANTTRANSACTION\": -1}," + "\"aggs\" : " + "{\"group_by_DATE\" : {\"date_histogram\" : " + "{\"field\" : \"DTCREATED\", \"interval\" : \"1M\"," + "\"format\" : \"yyyy-MM\" }," + "\"aggs\" : " + "{\"intraday_return\" : { \"sum\" : { \"field\" : \"amount\" } }}}}}}}"); ``` But it does not working. any idea what I am missing ? ---
**Author:** ![ywelsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ywelsch/32/7751_2.png) [@ywelsch](https://discuss.elastic.co/u/ywelsch)\ **Post date:** [November 9, 2016, 12:51pm UTC](https://discuss.elastic.co/t/elasticsearch-terms-group-by-and-sum-aggregation/65466/2 "2016-11-09T12:51:57Z")
which ES version is this? Is the filter supposed to be a [filter aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filter-aggregation.html)? If so, it must be properly wrapped in an `aggs` block. In older ES versions the top-level filter is a deprecated name for the [post\_filter](https://www.elastic.co/guide/en/elasticsearch/reference/5.0/search-request-post-filter.html), which filters the documents returned in the hits part of the response without affecting the documents used in the aggregations. ---
**Author:** ![aniketpatil](https://avatars.discourse-cdn.com/v4/letter/a/85f322/32.png) [@aniketpatil](https://discuss.elastic.co/u/aniketpatil)\ **Post date:** [November 9, 2016, 2:12pm UTC](https://discuss.elastic.co/t/elasticsearch-terms-group-by-and-sum-aggregation/65466/3 "2016-11-09T14:12:28Z")
elasticsearch-1.5.2 I have edited above query but it still showing sum of all amount month wise but not considering match condition. Here is the edited query ``` .source("{size:999999," + "\"_source\" : " + "[\"DTCREATED\", \"amount\"]" + " ,\"query\": { \"filtered\": {\"query\": { \"match\": { \"SUBMERCHANTTRANSACTION\": \"-1\"}}}}" + ",\"aggs\" : " + "{\"group_by_DATE\" : {\"date_histogram\" : " + "{\"field\" : \"DTCREATED\", \"interval\" : \"1M\"," + "\"format\" : \"yyyy-MM-dd\" }," + "\"aggs\" : " + "{\"sum_bar\" : { \"sum\" : { \"field\" : \"amount\" } }}}}}}}"); ``` ---
**Author:** ![ywelsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ywelsch/32/7751_2.png) [@ywelsch](https://discuss.elastic.co/u/ywelsch)\ **Post date:** [November 9, 2016, 2:41pm UTC](https://discuss.elastic.co/t/elasticsearch-terms-group-by-and-sum-aggregation/65466/4 "2016-11-09T14:41:51Z")
- why not use a [term filter](https://www.elastic.co/guide/en/elasticsearch/reference/1.5/query-dsl-term-filter.html) wrapped in a constant\_score query? - can you show your mapping (`curl -XGET 'http://localhost:9200/_mapping'`) ? This might help understand why it's not working. ---
**Author:** ![aniketpatil](https://avatars.discourse-cdn.com/v4/letter/a/85f322/32.png) [@aniketpatil](https://discuss.elastic.co/u/aniketpatil)\ **Post date:** [November 10, 2016, 7:22am UTC](https://discuss.elastic.co/t/elasticsearch-terms-group-by-and-sum-aggregation/65466/5 "2016-11-10T07:22:30Z")
I have shared mapping. I want term filter on SUBMERCHANTTRANSACTION aggregation for date and sum of amount ``` { "transaction" : { "mappings" : { "doc" : { "properties" : { "amount" : { "type" : "long" }, "DTCREATED" : { "type" : "date", "format" : "dateOptionalTime" }, "SUBMERCHANTTRANSACTION" : { "type" : "string" }, } } } } } ``` ---
**Author:** ![ywelsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ywelsch/32/7751_2.png) [@ywelsch](https://discuss.elastic.co/u/ywelsch)\ **Post date:** [November 10, 2016, 9:06am UTC](https://discuss.elastic.co/t/elasticsearch-terms-group-by-and-sum-aggregation/65466/6 "2016-11-10T09:06:22Z")
SUBMERCHANTTRANSACTION is an analyzed field in your mapping, which is probably not what you want. If it contains only numeric values, it should be a "long", otherwhise a non-analyzed string. The following query works for me: ```auto curl -XPOST 'http://localhost:9200/transaction' curl -XPUT 'http://localhost:9200/transaction/_mapping/doc' -d ' { "doc" : { "properties" : { "amount" : { "type" : "long" }, "DTCREATED" : { "type" : "date", "format" : "dateOptionalTime" }, "SUBMERCHANTTRANSACTION" : { "type" : "string", "index" : "not_analyzed" } } } } ' curl -XPOST 'http://localhost:9200/transaction/doc/1' -d '{"DTCREATED": "2016-09-19T18:30:00.000Z", "amount": 12, "SUBMERCHANTTRANSACTION": -1}' curl -XPOST 'http://localhost:9200/transaction/doc/2' -d '{"DTCREATED": "2016-09-20T18:30:00.000Z", "amount": 30, "SUBMERCHANTTRANSACTION": 0}' curl -XPOST 'http://localhost:9200/transaction/doc/3' -d '{"DTCREATED": "2016-10-25T18:30:00.000Z", "amount": 35, "SUBMERCHANTTRANSACTION": -1}' curl -XPOST 'http://localhost:9200/transaction/_refresh' curl -XPOST 'http://localhost:9200/transaction/_search?pretty' -d ' { "_source" : ["DTCREATED", "amount"], "query" : { "constant_score" : { "filter" : { "term" : { "SUBMERCHANTTRANSACTION" : -1 } } } }, "aggs" : { "group_by_DATE" : { "date_histogram" : { "field" : "DTCREATED", "interval" : "1M", "format" : "yyyy-MM-dd" }, "aggs" : { "sum_bar" : { "sum" : { "field" : "amount" } } } } } }' ``` which yields ```auto "took" : 3, "timed_out" : false, "_shards" : { "total" : 5, "successful" : 5, "failed" : 0 }, "hits" : { "total" : 2, "max_score" : 1.0, "hits" : [ { "_index" : "transaction", "_type" : "doc", "_id" : "1", "_score" : 1.0, "_source":{"DTCREATED":"2016-09-19T18:30:00.000Z","amount":12} }, { "_index" : "transaction", "_type" : "doc", "_id" : "3", "_score" : 1.0, "_source":{"DTCREATED":"2016-10-25T18:30:00.000Z","amount":35} } ] }, "aggregations" : { "group_by_DATE" : { "buckets" : [ { "key_as_string" : "2016-09-01", "key" : 1472688000000, "doc_count" : 1, "sum_bar" : { "value" : 12.0 } }, { "key_as_string" : "2016-10-01", "key" : 1475280000000, "doc_count" : 1, "sum_bar" : { "value" : 35.0 } } ] } } } ``` ---
**Author:** ![aniketpatil](https://avatars.discourse-cdn.com/v4/letter/a/85f322/32.png) [@aniketpatil](https://discuss.elastic.co/u/aniketpatil)\ **Post date:** [November 10, 2016, 9:38am UTC](https://discuss.elastic.co/t/elasticsearch-terms-group-by-and-sum-aggregation/65466/7 "2016-11-10T09:38:34Z")
Yes.. this is what I want. Thank you ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 8, 2016, 9:38am UTC](https://discuss.elastic.co/t/elasticsearch-terms-group-by-and-sum-aggregation/65466/8 "2016-12-08T09:38:46Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.