# Help with ES 1.x aggregation range filter over "sum" value

**URL:** <https://discuss.elastic.co/t/help-with-es-1-x-aggregation-range-filter-over-sum-value/17735>\
**Category:** Elasticsearch\
**Created:** [May 27, 2014, 2:06am UTC](https://discuss.elastic.co/t/help-with-es-1-x-aggregation-range-filter-over-sum-value/17735 "2014-05-27T02:06:35Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Choon\_Keat\_Chew](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/choon_keat_chew/32/1535_2.png) [@Choon\_Keat\_Chew](https://discuss.elastic.co/u/Choon_Keat_Chew)\
**Post date:** [May 27, 2014, 2:06am UTC](https://discuss.elastic.co/t/help-with-es-1-x-aggregation-range-filter-over-sum-value/17735/1 "2014-05-27T02:06:35Z")

</div>

I have an index full of user transactions. credit is the amount of an  
individual transaction. To get a list of users with most total credits I  
have this working fine

{  
"size": 0, // only interested in aggregation info  
"aggs": {  
"users": {  
"terms": {  
"field": "user\_id",  
"order": {  
"total\_sum.sum": "desc"  
}  
},  
"aggs": {  
"total\_sum": {  
"sum": {  
"field": "credit"  
}  
}  
}  
}  
}  
}

which is roughly equivalent in SQL:  
SELECT user\_id, SUM(credit) FROM index ORDER BY SUM(credit) DESC LIMIT 10;

I'm having problems modifying my ES query to filter by total\_sum.sum ,  
which in SQL equivalent, it would be:  
SELECT player\_id, SUM(points) FROM index _HAVING SUM(POINTS) BETWEEN 10 AND  
15_ ORDER BY SUM(POINTS) DESC LIMIT 10;

My attempts inserting range filters fails in syntax

{  
"size": 0,  
"aggs": {  
"users": {  
"terms": {  
"field": "user\_id",  
"order": {  
"total\_sum.sum": "desc"  
}  
},  
"aggs": {  
"having": {  
"filter": {  
"range": {  
"total\_sum.sum": {  
"gte": 10, "lte": 15  
}  
}  
},  
"aggs": {  
"total\_sum": {  
"sum": {  
"field": "credit"  
}  
}  
}  
}  
}  
}  
}  
}

get errors

{  
"error": "SearchPhaseExecutionException[Failed to execute phase  
[query\_fetch], all shards failed; shardFailures ...  
[search/phase/query+fetch]]; nested: AggregationExecutionException[terms  
aggregation [users] is configured with a sub-aggregation order [total\_sum]  
but no sub aggregation with this name is configured]; }]",  
"status": 500  
}

Is such a range filter possible?

-- choonkeat

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/a2092306-7eed-4803-811f-daa999e8b372%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/a2092306-7eed-4803-811f-daa999e8b372%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [May 27, 2014, 10:33am UTC](https://discuss.elastic.co/t/help-with-es-1-x-aggregation-range-filter-over-sum-value/17735/2 "2014-05-27T10:33:25Z")

</div>

Hi,

Filtering based on results of aggregations is not supported unfortunately.  
There is no way to do the equivalent of your SQL query.

On Tue, May 27, 2014 at 4:06 AM, Choon Keat Chew [choonkeat@gmail.com](mailto:choonkeat@gmail.com)wrote:

> I have an index full of user transactions. credit is the amount of an  
> individual transaction. To get a list of users with most total credits I  
> have this working fine
> 
> {  
> "size": 0, // only interested in aggregation info  
> "aggs": {  
> "users": {  
> "terms": {  
> "field": "user\_id",  
> "order": {  
> "total\_sum.sum": "desc"  
> }  
> },  
> "aggs": {  
> "total\_sum": {  
> "sum": {  
> "field": "credit"  
> }  
> }  
> }  
> }  
> }  
> }
> 
> which is roughly equivalent in SQL:  
> SELECT user\_id, SUM(credit) FROM index ORDER BY SUM(credit) DESC LIMIT 10;
> 
> I'm having problems modifying my ES query to filter by total\_sum.sum ,  
> which in SQL equivalent, it would be:  
> SELECT player\_id, SUM(points) FROM index _HAVING SUM(POINTS) BETWEEN 10  
> AND 15_ ORDER BY SUM(POINTS) DESC LIMIT 10;
> 
> My attempts inserting range filters fails in syntax
> 
> {  
> "size": 0,  
> "aggs": {  
> "users": {  
> "terms": {  
> "field": "user\_id",  
> "order": {  
> "total\_sum.sum": "desc"  
> }  
> },  
> "aggs": {  
> "having": {  
> "filter": {  
> "range": {  
> "total\_sum.sum": {  
> "gte": 10, "lte": 15  
> }  
> }  
> },  
> "aggs": {  
> "total\_sum": {  
> "sum": {  
> "field": "credit"  
> }  
> }  
> }  
> }  
> }  
> }  
> }  
> }
> 
> get errors
> 
> {  
> "error": "SearchPhaseExecutionException[Failed to execute phase  
> [query\_fetch], all shards failed; shardFailures ...  
> [search/phase/query+fetch]]; nested: AggregationExecutionException[terms  
> aggregation [users] is configured with a sub-aggregation order [total\_sum]  
> but no sub aggregation with this name is configured]; }]",  
> "status": 500  
> }
> 
> Is such a range filter possible?
> 
> -- choonkeat
> 
> --  
> You received this message because you are subscribed to the Google Groups  
> "elasticsearch" group.  
> To unsubscribe from this group and stop receiving emails from it, send an  
> email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
> To view this discussion on the web visit  
> [https://groups.google.com/d/msgid/elasticsearch/a2092306-7eed-4803-811f-daa999e8b372%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/a2092306-7eed-4803-811f-daa999e8b372%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/a2092306-7eed-4803-811f-daa999e8b372%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/a2092306-7eed-4803-811f-daa999e8b372%40googlegroups.com?utm_medium=email&utm_source=footer)  
> .  
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

--  
Adrien Grand

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6JpuT%3DZpPwxqz3QL%2Bv5Q4RqH10GSEm7xmjDDXX3mbTAA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6JpuT%3DZpPwxqz3QL%2Bv5Q4RqH10GSEm7xmjDDXX3mbTAA%40mail.gmail.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<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 6, 2017, 1:26am UTC](https://discuss.elastic.co/t/help-with-es-1-x-aggregation-range-filter-over-sum-value/17735/3 "2017-07-06T01:26:44Z")

</div>