# ElasticSearch - how to SUM function\_score by field **URL:** \ **Category:** Elasticsearch\ **Created:** [March 7, 2014, 10:04pm UTC](https://discuss.elastic.co/t/elasticsearch-how-to-sum-function-score-by-field/16240 "2014-03-07T22:04:40Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Jay\_Hilden](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jay_hilden/32/877_2.png) [@Jay\_Hilden](https://discuss.elastic.co/u/Jay_Hilden)\ **Post date:** [March 7, 2014, 10:04pm UTC](https://discuss.elastic.co/t/elasticsearch-how-to-sum-function-score-by-field/16240/1 "2014-03-07T22:04:40Z")
I have a query that is using function\_score to rank the results. Here is a sample of what is returned: { "\_index": "clone", "\_type": "authEvent", "\_id": "6431823", "\_score": 4.8, "fields": { "authInput.uID": "MPXWDKW2P", "authResult.productValue": 1, "authInput.userName": "F936F3AA-E26C-48DB-BDBC-44956B634260", "authResult.authEventDate": "2014-02-27T09:29:30.703125-06:00", "authResult.rulesFailed": [ "AuthCountByUser" ] } } What I want to is take the results and run the equivalent of this SQL statement: SELECT TOP 20 "authInput.userName", SUM("\_score") FROM foo GROUP BY "authInput.userName" ORDER BY SUM("\_score") DESC How can I do this with ES? NOTE: I'm using ES 0.9x, we will be moving to 1.0.0 soon but we have not yet. Thanks in advance! -- 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/dc11fcb9-8954-4d0a-8996-afd1f73290f4%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/dc11fcb9-8954-4d0a-8996-afd1f73290f4%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Binh\_Ly\_2](https://avatars.discourse-cdn.com/v4/letter/b/d07c76/32.png) [@Binh\_Ly\_2](https://discuss.elastic.co/u/Binh_Ly_2)\ **Post date:** [March 10, 2014, 6:09pm UTC](https://discuss.elastic.co/t/elasticsearch-how-to-sum-function-score-by-field/16240/2 "2014-03-10T18:09:10Z")
Unfortunately, subqueries are not supported. What you can do is dump the results of your first query into an index. And then you can do a terms\_stats facet on that dump to get your final results: [http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-facets-terms-stats-facet.html](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-facets-terms-stats-facet.html) -- 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/04770395-4c81-4d42-9566-df763a39fb0c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/04770395-4c81-4d42-9566-df763a39fb0c%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Jay\_Hilden](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jay_hilden/32/877_2.png) [@Jay\_Hilden](https://discuss.elastic.co/u/Jay_Hilden)\ **Post date:** [March 10, 2014, 6:15pm UTC](https://discuss.elastic.co/t/elasticsearch-how-to-sum-function-score-by-field/16240/3 "2014-03-10T18:15:10Z")
Thanks for the response Binh. Is there a way to pipe the response from my first query directly into a new index without round tripping the data back to the server that requested the query? On Mon, Mar 10, 2014 at 1:09 PM, Binh Ly [binhly\_es@yahoo.com](mailto:binhly_es@yahoo.com) wrote: > Unfortunately, subqueries are not supported. What you can do is dump the > results of your first query into an index. And then you can do a > terms\_stats facet on that dump to get your final results: > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-facets-terms-stats-facet.html) > > -- > You received this message because you are subscribed to a topic in the > Google Groups "elasticsearch" group. > To unsubscribe from this topic, visit > [https://groups.google.com/d/topic/elasticsearch/hiTA1hsdLr8/unsubscribe](https://groups.google.com/d/topic/elasticsearch/hiTA1hsdLr8/unsubscribe). > To unsubscribe from this group and all its topics, 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/04770395-4c81-4d42-9566-df763a39fb0c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/04770395-4c81-4d42-9566-df763a39fb0c%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/04770395-4c81-4d42-9566-df763a39fb0c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/04770395-4c81-4d42-9566-df763a39fb0c%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- 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/CAD3qxy56ohK9icugkoo2vebJkyXLNZfBpQterFO2ib7zU1aXFQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAD3qxy56ohK9icugkoo2vebJkyXLNZfBpQterFO2ib7zU1aXFQ%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**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:44am UTC](https://discuss.elastic.co/t/elasticsearch-how-to-sum-function-score-by-field/16240/4 "2017-07-06T01:44:27Z")