# Range on Sum Aggregation
**URL:** https://discuss.elastic.co/t/range-on-sum-aggregation/131066
**Category:** Elasticsearch
**Created:** [May 8, 2018, 9:16pm UTC](https://discuss.elastic.co/t/range-on-sum-aggregation/131066 "2018-05-08T21:16:30Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Vijay\_arora1](https://discuss.elastic.co/u/Vijay_arora1)
#### Post date: [May 8, 2018, 9:16pm UTC](https://discuss.elastic.co/t/range-on-sum-aggregation/131066/1 "2018-05-08T21:16:30Z")
I am trying to write a query which would give me the count of Ids where sum total of transactions fall into a certain range.
In terms of SQL query something like:
```auto
Select ProductId from SaleTransactions where SalesQuarter='Q1' group by ProductId having Sum(SalesAmount) > 100 and Sum(SalesAmount) <1000 having SalesQuarter =Q1
```
The index mapping is like:
```auto
"mappings" : {
"product" : {
"properties": {
"ProductId: : { "type" : "keyword"},
"SaleTransactions" : {
"type": "nested",
"include_in_parent" : true,
"id": {"type": "keyword"},
"Product_Id": {"type": "keyword"},
"sale_amount": {"type": "double"},
"SalesQuarter": {"type": "keyword" }
}
}
}
}
```
I tried applying the Sum Aggregation and then using a bucket\_selector to get the range, and that gives me multiple buckets, one for each id. I am not sure if this is the best way, especially when we have a large transnational volume, is there a better way to do it by either applying a value\_count on it or mixing the range aggregation on sum aggregation.
Here is my sample aggs query:
```auto
"aggs":{
"aggs1":{
"terms":{
"field":"SaleTransactions.Product_Id"
},
"aggs":{
"filtered-entities":{
"filter":{
"terms":{
"SaleTransactions.SalesQuarter":[
"Q1"
]
}
},
"aggs":{
"sum_Total":{
"sum":{
"field":"SaleTransactions.sale_amount"
}
}
}
},
"sumTotal-filter":{
"bucket_selector":{
"buckets_path":{
"sumTotal":"filtered-entities>sum_Total"
},
"script":"params.sumTotal > 500 && params.sumTotal <= 5000"
}
}
}
}
}
```
Thanks
---
### Author:  [@polyfractal](https://discuss.elastic.co/u/polyfractal)
#### Post date: [May 10, 2018, 4:50pm UTC](https://discuss.elastic.co/t/range-on-sum-aggregation/131066/2 "2018-05-10T16:50:56Z")
> [@Vijay\_arora1](#):
>
> I tried applying the Sum Aggregation and then using a bucket\_selector to get the range, and that gives me multiple buckets, one for each id. I am not sure if this is the best way, especially when we have a large transnational volume, is there a better way to do it by either applying a value\_count on it or mixing the range aggregation on sum aggregation.
Your agg is good... this is the way to do it. You have to first aggregate together all the docs and sum up individual `sale_amount`'s for each ID. Only after all the values have been summed can you filter the range, via the `bucket_selector`. Which leaves you the IDs that have sale amounts inside the range, which you can count up (either client-side or by another pipeline).
The concern over large number of transactions is valid though. The terms aggregation is optimized for "top-n" scenarios. If you need to return a very large number of terms, or all of them, you should instead checkout using the newish `composite` aggregation: [Composite aggregation | Elasticsearch Guide [8.11] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html)
The composite agg is designed to paginate/scroll over the buckets of an aggregation, allowing you to get all the results in a manner that won't destroy the cluster. 🙂
---
### Author:  [@Vijay\_arora1](https://discuss.elastic.co/u/Vijay_arora1)
#### Post date: [May 11, 2018, 3:11am UTC](https://discuss.elastic.co/t/range-on-sum-aggregation/131066/3 "2018-05-11T03:11:01Z")
Thanks for pointing out to composite aggregation.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [June 8, 2018, 3:11am UTC](https://discuss.elastic.co/t/range-on-sum-aggregation/131066/4 "2018-06-08T03:11:06Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.