# Need help with Sum **URL:** \ **Category:** Elasticsearch\ **Created:** [April 16, 2013, 9:07pm UTC](https://discuss.elastic.co/t/need-help-with-sum/11605 "2013-04-16T21:07:05Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![OffensivelyBad\_2](https://avatars.discourse-cdn.com/v4/letter/o/51bf81/32.png) [@OffensivelyBad\_2](https://discuss.elastic.co/u/OffensivelyBad_2)\ **Post date:** [April 16, 2013, 9:07pm UTC](https://discuss.elastic.co/t/need-help-with-sum/11605/1 "2013-04-16T21:07:05Z")
I am new to Elasticsearch. I'm trying to write a query that will group by a field and calculate a sum. In SQL, my query would look like this: SELECT lane, SUM(routes) FROM lanes GROUP BY lane I want to essentially run the same query in ES as I did in SQL, so that my result would be something like (in json of course): M05: 7103, M03: 9740 I have this data that looks like this in ES: { "\_index" : "kpi", "\_type" : "mroutes\_by\_lane", "\_id" : "TUeWFEhnS9q1Ukb2QdZABg"", "\_score" : 1.0, "\_source" : {"warehouse\_id":107,"date":"2013-04-08","lane" :"M05","routes":4047} }, { "\_index" : "kpi", "\_type" : "mroutes\_by\_lane", "\_id" : "owVmGW9GT562\_2Alfru2DA", "\_score" : 1.0, "\_source" : {"warehouse\_id":107,"date":"2013-04-08","lane" :"M03","routes":4065} },{ "\_index" : "kpi", "\_type" : "mroutes\_by\_lane", "\_id" : "JY9xNDxqSsajw76oMC2gxA", "\_score" : 1.0, "\_source" : {"warehouse\_id":107,"date":"2013-04-08","lane" :"M05","routes":3056} },{ "\_index" : "kpi", "\_type" : "mroutes\_by\_lane", "\_id" : "owVmGW9GT345\_2Alfru2DB", "\_score" : 1.0, "\_source" : {"warehouse\_id":107,"date":"2013-04-08","lane" :"M03","routes":5675} },... -- 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). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\ **Post date:** [April 16, 2013, 10:21pm UTC](https://discuss.elastic.co/t/need-help-with-sum/11605/2 "2013-04-16T22:21:19Z")
As I already mentioned on stackoverflow, you can use terms stats facets[http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet/](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet/) for that. On Tuesday, April 16, 2013 5:07:05 PM UTC-4, OffensivelyBad wrote: > I am new to Elasticsearch. I'm trying to write a query that will group > by a field and calculate a sum. In SQL, my query would look like this: SELECT > lane, SUM(routes) FROM lanes GROUP BY lane > > I want to essentially run the same query in ES as I did in SQL, so that my > result would be something like (in json of course): M05: 7103, M03: 9740 > I have this data that looks like this in ES: > > { > "\_index" : "kpi", > "\_type" : "mroutes\_by\_lane", > "\_id" : "TUeWFEhnS9q1Ukb2QdZABg"", > "\_score" : 1.0, "\_source" : {"warehouse\_id":107,"date":"2013-04-08","lane" :"M05","routes":4047} > }, { > "\_index" : "kpi", > "\_type" : "mroutes\_by\_lane", > "\_id" : "owVmGW9GT562\_2Alfru2DA", > "\_score" : 1.0, "\_source" : {"warehouse\_id":107,"date":"2013-04-08","lane" :"M03","routes":4065} > },{ > "\_index" : "kpi", > "\_type" : "mroutes\_by\_lane", > "\_id" : "JY9xNDxqSsajw76oMC2gxA", > "\_score" : 1.0, "\_source" : {"warehouse\_id":107,"date":"2013-04-08","lane" :"M05","routes":3056} > },{ > "\_index" : "kpi", > "\_type" : "mroutes\_by\_lane", > "\_id" : "owVmGW9GT345\_2Alfru2DB", > "\_score" : 1.0, "\_source" : {"warehouse\_id":107,"date":"2013-04-08","lane" :"M03","routes":5675} > },... -- 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). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Kang\_min\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kang_min_liu/32/1220_2.png) [@Kang\_min\_Liu](https://discuss.elastic.co/u/Kang_min_Liu)\ **Post date:** [April 16, 2013, 11:24pm UTC](https://discuss.elastic.co/t/need-help-with-sum/11605/3 "2013-04-16T23:24:26Z")
I guess you could use statistical facet to achieve the purposes ``` http://www.elasticsearch.org/guide/reference/api/search/facets/statistical-facet/ ``` { "query" : { "match\_all" : {} }, "facets" : { "sumOfRoutes" : { "statistical" : { "field" : "routes" } } } } Though the response contains much more then just the sum. -- 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). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![OffensivelyBad\_2](https://avatars.discourse-cdn.com/v4/letter/o/51bf81/32.png) [@OffensivelyBad\_2](https://discuss.elastic.co/u/OffensivelyBad_2)\ **Post date:** [April 17, 2013, 2:07pm UTC](https://discuss.elastic.co/t/need-help-with-sum/11605/4 "2013-04-17T14:07:55Z")
Thanks Igor and gugod. Just what I needed. On Tuesday, April 16, 2013 5:07:05 PM UTC-4, OffensivelyBad wrote: > I am new to Elasticsearch. I'm trying to write a query that will group > by a field and calculate a sum. In SQL, my query would look like this: SELECT > lane, SUM(routes) FROM lanes GROUP BY lane > > I want to essentially run the same query in ES as I did in SQL, so that my > result would be something like (in json of course): M05: 7103, M03: 9740 > I have this data that looks like this in ES: > > { > "\_index" : "kpi", > "\_type" : "mroutes\_by\_lane", > "\_id" : "TUeWFEhnS9q1Ukb2QdZABg"", > "\_score" : 1.0, "\_source" : {"warehouse\_id":107,"date":"2013-04-08","lane" :"M05","routes":4047} > }, { > "\_index" : "kpi", > "\_type" : "mroutes\_by\_lane", > "\_id" : "owVmGW9GT562\_2Alfru2DA", > "\_score" : 1.0, "\_source" : {"warehouse\_id":107,"date":"2013-04-08","lane" :"M03","routes":4065} > },{ > "\_index" : "kpi", > "\_type" : "mroutes\_by\_lane", > "\_id" : "JY9xNDxqSsajw76oMC2gxA", > "\_score" : 1.0, "\_source" : {"warehouse\_id":107,"date":"2013-04-08","lane" :"M05","routes":3056} > },{ > "\_index" : "kpi", > "\_type" : "mroutes\_by\_lane", > "\_id" : "owVmGW9GT345\_2Alfru2DB", > "\_score" : 1.0, "\_source" : {"warehouse\_id":107,"date":"2013-04-08","lane" :"M03","routes":5675} > },... -- 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). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**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, 2:40am UTC](https://discuss.elastic.co/t/need-help-with-sum/11605/5 "2017-07-06T02:40:49Z")