# Calculating sum of nested fields with date\_histogram aggregation

**URL:** <https://discuss.elastic.co/t/calculating-sum-of-nested-fields-with-date-histogram-aggregation/17786>\
**Category:** Elasticsearch\
**Created:** [May 28, 2014, 4:34pm UTC](https://discuss.elastic.co/t/calculating-sum-of-nested-fields-with-date-histogram-aggregation/17786 "2014-05-28T16:34:52Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![David\_5](https://avatars.discourse-cdn.com/v4/letter/d/49beb7/32.png) [@David\_5](https://discuss.elastic.co/u/David_5)\
**Post date:** [May 28, 2014, 4:34pm UTC](https://discuss.elastic.co/t/calculating-sum-of-nested-fields-with-date-histogram-aggregation/17786/1 "2014-05-28T16:34:52Z")

</div>

Hello,

I have a mapping that looks like this:

"client" : {  
// various irrelevant stuff here...

"associated\_transactions" : {  
"type" : "nested",  
"include\_in\_parent" : true,  
"properties" : {  
"amount" : {  
"type" : "double"  
},  
"effective\_at" : {  
"type" : "date",  
"format" : "dateOptionalTime"  
}  
}  
}  
}

I'm trying to get a date\_histogram that shows total revenue across all  
clients--i.e. a time series showing the sum associated\_transactions.amount  
in a histogram determined by associated\_transactions.effective\_date. I  
tried running this query:

{  
"query": {  
// ...  
},  
"aggregations": {  
"revenue": {  
"date\_histogram": {  
"interval": "month",  
"min\_doc\_count": 0,  
"field": "associated\_transactions.effective\_at"  
},  
"aggs": {  
"monthly\_revenue": {  
"sum": {  
"field": "associated\_transactions.amount"  
}  
}  
}  
}  
}  
}

But the sum it's giving me isn't right. It seems that what ES is doing is  
finding all clients who have any transaction in a given month, then summing  
all of the transactions (from any time) for any client who made a purchase  
in a given month. That is, it's a \*sum of the amount spent in the lifetime  
\*of a client who made a purchase in a given month, not the _sum of  
purchases in a give month_.

Is there any way to get the data I'm looking for, or is this a limitation  
in how ES handles nested fields?

Thanks very much in advance for your help!

David

--  
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/b954b9b5-49dd-4ccb-9e38-7321acfd2623%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/b954b9b5-49dd-4ccb-9e38-7321acfd2623%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 30, 2014, 9:09am UTC](https://discuss.elastic.co/t/calculating-sum-of-nested-fields-with-date-histogram-aggregation/17786/2 "2014-05-30T09:09:37Z")

</div>

Indeed, your aggregation runs in the context of the root document. You need  
to use a nested aggregation to tell Elasticsearch to use your nested field  
as a context:

"aggs": {  
"transactions": {  
"nested": {  
"path": "associated\_transactions"  
},  
"aggs": {  
"revenue": {  
"date\_histogram": {  
"interval": "month",  
"min\_doc\_count": 0,  
"field": "associated\_transactions.effective\_at"  
},  
"aggs": {  
"monthly\_revenue": {  
"sum": {  
"field": "associated\_transactions.amount"  
}  
}  
}  
}  
}  
}  
}

See

> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.

for more information.

On Wed, May 28, 2014 at 6:34 PM, David [david@carom.io](mailto:david@carom.io) wrote:

> Hello,
> 
> I have a mapping that looks like this:
> 
> "client" : {  
> // various irrelevant stuff here...
> 
> "associated\_transactions" : {  
> "type" : "nested",  
> "include\_in\_parent" : true,  
> "properties" : {  
> "amount" : {  
> "type" : "double"  
> },  
> "effective\_at" : {  
> "type" : "date",  
> "format" : "dateOptionalTime"  
> }  
> }  
> }  
> }
> 
> I'm trying to get a date\_histogram that shows total revenue across all  
> clients--i.e. a time series showing the sum associated\_transactions.amount  
> in a histogram determined by associated\_transactions.effective\_date. I  
> tried running this query:
> 
> {  
> "query": {  
> // ...  
> },  
> "aggregations": {  
> "revenue": {  
> "date\_histogram": {  
> "interval": "month",  
> "min\_doc\_count": 0,  
> "field": "associated\_transactions.effective\_at"  
> },  
> "aggs": {  
> "monthly\_revenue": {  
> "sum": {  
> "field": "associated\_transactions.amount"  
> }  
> }  
> }  
> }  
> }  
> }
> 
> But the sum it's giving me isn't right. It seems that what ES is doing is  
> finding all clients who have any transaction in a given month, then summing  
> all of the transactions (from any time) for any client who made a purchase  
> in a given month. That is, it's a \*sum of the amount spent in the  
> lifetime \*of a client who made a purchase in a given month, not the _sum  
> of purchases in a give month_.
> 
> Is there any way to get the data I'm looking for, or is this a limitation  
> in how ES handles nested fields?
> 
> Thanks very much in advance for your help!
> 
> David
> 
> --  
> 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/b954b9b5-49dd-4ccb-9e38-7321acfd2623%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/b954b9b5-49dd-4ccb-9e38-7321acfd2623%40googlegroups.com)  
> [https://groups.google.com/d/msgid/elasticsearch/b954b9b5-49dd-4ccb-9e38-7321acfd2623%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/b954b9b5-49dd-4ccb-9e38-7321acfd2623%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/CAL6Z4j610B1G%3D2ouZcYOGuEeyBUXaXrZKd%2BLpKWRNQyYQq%3D3Vg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j610B1G%3D2ouZcYOGuEeyBUXaXrZKd%2BLpKWRNQyYQq%3D3Vg%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:25am UTC](https://discuss.elastic.co/t/calculating-sum-of-nested-fields-with-date-histogram-aggregation/17786/3 "2017-07-06T01:25:51Z")

</div>