# Can we do statistical facets on groups in a single query? **URL:** \ **Category:** Elasticsearch\ **Created:** [November 21, 2013, 8:10am UTC](https://discuss.elastic.co/t/can-we-do-statistical-facets-on-groups-in-a-single-query/14510 "2013-11-21T08:10:09Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![rakesh\_rakshit](https://avatars.discourse-cdn.com/v4/letter/r/8e8cbc/32.png) [@rakesh\_rakshit](https://discuss.elastic.co/u/rakesh_rakshit)\ **Post date:** [November 21, 2013, 8:10am UTC](https://discuss.elastic.co/t/can-we-do-statistical-facets-on-groups-in-a-single-query/14510/1 "2013-11-21T08:10:09Z")
Hi all, I have say five fields A,B,C,D and E. I am able to do statistical facet (min,max,sum,mean) over field E over records satisfying some conditions in where clause. Now I have A as the country and I want to group the records (say country A1, A2, A3 and so on) and apply statistical facets on field E over those groups using a single query. Is it possible to do this? If yes how ? Please help. Thanks in advance for your help. Regards, Rakesh Kumar Rakshit -- 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:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\ **Post date:** [November 21, 2013, 9:35am UTC](https://discuss.elastic.co/t/can-we-do-statistical-facets-on-groups-in-a-single-query/14510/2 "2013-11-21T09:35:21Z")
Hey, Check out the script field for the statistical facet, that might help you > **[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. --Alex On Thu, Nov 21, 2013 at 9:10 AM, rakesh rakshit \<[ihavethepotential@gmail.com](mailto:ihavethepotential@gmail.com) > wrote: > Hi all, > > I have say five fields A,B,C,D and E. I am able to do statistical facet > (min,max,sum,mean) over field E over records satisfying some conditions in > where clause. > > Now I have A as the country and I want to group the records (say country > A1, A2, A3 and so on) and apply statistical facets on field E over those > groups using a single query. > > Is it possible to do this? If yes how ? > > Please help. Thanks in advance for your help. > > Regards, > Rakesh Kumar Rakshit > > -- > 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). -- 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:** ![Ramchandra\_Phadake](https://avatars.discourse-cdn.com/v4/letter/r/d9b06d/32.png) [@Ramchandra\_Phadake](https://discuss.elastic.co/u/Ramchandra_Phadake)\ **Post date:** [November 21, 2013, 10:25am UTC](https://discuss.elastic.co/t/can-we-do-statistical-facets-on-groups-in-a-single-query/14510/3 "2013-11-21T10:25:32Z")
Check out terms stat fact > **[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. Thanks, Ram On Thursday, November 21, 2013 3:05:21 PM UTC+5:30, Alexander Reelsen wrote: > Hey, > > Check out the script field for the statistical facet, that might help you > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-facets-statistical-facet.html) > > --Alex > > On Thu, Nov 21, 2013 at 9:10 AM, rakesh rakshit \<[ihavethe...@gmail.com](mailto:ihavethe...@gmail.com)\ > > > wrote: > > > Hi all, > > > > I have say five fields A,B,C,D and E. I am able to do statistical facet > > (min,max,sum,mean) over field E over records satisfying some conditions in > > where clause. > > > > Now I have A as the country and I want to group the records (say country > > A1, A2, A3 and so on) and apply statistical facets on field E over those > > groups using a single query. > > > > Is it possible to do this? If yes how ? > > > > Please help. Thanks in advance for your help. > > > > Regards, > > Rakesh Kumar Rakshit > > > > -- > > 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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- 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:** ![rakesh\_rakshit](https://avatars.discourse-cdn.com/v4/letter/r/8e8cbc/32.png) [@rakesh\_rakshit](https://discuss.elastic.co/u/rakesh_rakshit)\ **Post date:** [November 21, 2013, 1:28pm UTC](https://discuss.elastic.co/t/can-we-do-statistical-facets-on-groups-in-a-single-query/14510/4 "2013-11-21T13:28:50Z")
Hi Alex, Thanks for the reply. I went through script field and found it is required to do statistical computation involving a numeric value derived from the script you will write. I cannot find if it allows you apply facets in subsets/groups. For example, consider the following data: {"country" : "1", "population":"100"} {"country" : "1", "population":"200"} {"country" : "2", "population":"100"} {"country" : "2", "population":"200"} {"country" : "1", "population":"500"} I want to compute total population groupby country. So the expected results will be : {"country" : "1", "total":"800"},{"country" : "2", "total":"300"} Please let me know if you feel this can be done by script field in ES. Thanks, Rakesh On Thursday, November 21, 2013 3:05:21 PM UTC+5:30, Alexander Reelsen wrote: > Hey, > > Check out the script field for the statistical facet, that might help you > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-facets-statistical-facet.html) > > --Alex > > On Thu, Nov 21, 2013 at 9:10 AM, rakesh rakshit \<[ihavethe...@gmail.com](mailto:ihavethe...@gmail.com)\ > > > wrote: > > > Hi all, > > > > I have say five fields A,B,C,D and E. I am able to do statistical facet > > (min,max,sum,mean) over field E over records satisfying some conditions in > > where clause. > > > > Now I have A as the country and I want to group the records (say country > > A1, A2, A3 and so on) and apply statistical facets on field E over those > > groups using a single query. > > > > Is it possible to do this? If yes how ? > > > > Please help. Thanks in advance for your help. > > > > Regards, > > Rakesh Kumar Rakshit > > > > -- > > 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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- 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:** ![Hannes\_Korte](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hannes_korte/32/909_2.png) [@Hannes\_Korte](https://discuss.elastic.co/u/Hannes_Korte)\ **Post date:** [November 21, 2013, 2:05pm UTC](https://discuss.elastic.co/t/can-we-do-statistical-facets-on-groups-in-a-single-query/14510/5 "2013-11-21T14:05:16Z")
Hi Rakesh, you can use the terms stats facet for this: { "facets": { "countryPopulation": { "terms\_stats": { "key\_field": "country", "value\_field": "population" } } } } This will give you the following: "terms": [ { "term": "1", "count": 3, "total\_count": 3, "min": 100, "max": 500, "total": 800, "mean": 266.6666666666667 }, { "term": "2", "count": 2, "total\_count": 2, "min": 100, "max": 200, "total": 300, "mean": 150 } ] Best regards Hannes On 21.11.2013 14:28, rakesh rakshit wrote: > Hi Alex, > > Thanks for the reply. I went through script field and found it is required > to do statistical computation involving a numeric value derived from the > script you will write. I cannot find if it allows you apply facets in > subsets/groups. > > For example, consider the following data: > > {"country" : "1", "population":"100"} > {"country" : "1", "population":"200"} > {"country" : "2", "population":"100"} > {"country" : "2", "population":"200"} > {"country" : "1", "population":"500"} > > I want to compute total population groupby country. So the expected results > will be : > > {"country" : "1", "total":"800"},{"country" : "2", "total":"300"} > > Please let me know if you feel this can be done by script field in ES. > > Thanks, > Rakesh > > On Thursday, November 21, 2013 3:05:21 PM UTC+5:30, Alexander Reelsen wrote: > > > Hey, > > > > Check out the script field for the statistical facet, that might help you > > > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-facets-statistical-facet.html) > > > > --Alex > > > > On Thu, Nov 21, 2013 at 9:10 AM, rakesh rakshit \<[ihavethe...@gmail.com](mailto:ihavethe...@gmail.com)\ > > > > > wrote: > > > > > Hi all, > > > > > > I have say five fields A,B,C,D and E. I am able to do statistical facet > > > (min,max,sum,mean) over field E over records satisfying some conditions in > > > where clause. > > > > > > Now I have A as the country and I want to group the records (say country > > > A1, A2, A3 and so on) and apply statistical facets on field E over those > > > groups using a single query. > > > > > > Is it possible to do this? If yes how ? > > > > > > Please help. Thanks in advance for your help. > > > > > > Regards, > > > Rakesh Kumar Rakshit > > > > > > -- > > > 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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- 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:05am UTC](https://discuss.elastic.co/t/can-we-do-statistical-facets-on-groups-in-a-single-query/14510/6 "2017-07-06T02:05:40Z")