# Aggregate multiple value fields separately in terms\_stats facet? **URL:** \ **Category:** Elasticsearch\ **Created:** [July 8, 2011, 4:48am UTC](https://discuss.elastic.co/t/aggregate-multiple-value-fields-separately-in-terms-stats-facet/4816 "2011-07-08T04:48:53Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Hari\_Shankar](https://avatars.discourse-cdn.com/v4/letter/h/ad7895/32.png) [@Hari\_Shankar](https://discuss.elastic.co/u/Hari_Shankar)\ **Post date:** [July 8, 2011, 4:48am UTC](https://discuss.elastic.co/t/aggregate-multiple-value-fields-separately-in-terms-stats-facet/4816/1 "2011-07-08T04:48:53Z")
Hi, I tried giving multiple value\_fields in the terms stats facet. But then it seems to add up all the value fields when calculating the statistics. Can I instead make it compute the stats separately for each field that I specify in the value\_field? That is, do the grouping on the basis of the key\_field as usual, but give me the statistics for multiple fields. Is that possible in the current version (0.16)? Thanks, Hari ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [July 8, 2011, 4:54am UTC](https://discuss.elastic.co/t/aggregate-multiple-value-fields-separately-in-terms-stats-facet/4816/2 "2011-07-08T04:54:24Z")
You can simply use multiple terms stats facets, one per value field (with the same key field). On Friday, July 8, 2011 at 7:48 AM, Hari Shankar wrote: > Hi, > > I tried giving multiple value\_fields in the terms stats facet. But then it seems to add up all the value fields when calculating the statistics. Can I instead make it compute the stats separately for each field that I specify in the value\_field? That is, do the grouping on the basis of the key\_field as usual, but give me the statistics for multiple fields. Is that possible in the current version (0.16)? > > Thanks, > Hari ---
**Author:** ![Hari\_Shankar](https://avatars.discourse-cdn.com/v4/letter/h/ad7895/32.png) [@Hari\_Shankar](https://discuss.elastic.co/u/Hari_Shankar)\ **Post date:** [July 8, 2011, 6:47am UTC](https://discuss.elastic.co/t/aggregate-multiple-value-fields-separately-in-terms-stats-facet/4816/3 "2011-07-08T06:47:06Z")
Yep I thought of that but I would like to maintain order based on one particular field. e.g, I want to top 20 terms based on field 1, but for those 20 terms I want aggregations on field 2 and field 3 also. Right now the way I do this is I do the aggregation based on field 1, I set the order to "total", then I have to do a second query with a filter for these terms that were returned in the first query. On Fri, Jul 8, 2011 at 10:24 AM, Shay Banon [shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com)wrote: > You can simply use multiple terms stats facets, one per value field (with > the same key field). > > On Friday, July 8, 2011 at 7:48 AM, Hari Shankar wrote: > > Hi, > > I tried giving multiple value\_fields in the terms stats facet. But then it > seems to add up all the value fields when calculating the statistics. Can I > instead make it compute the stats separately for each field that I specify > in the value\_field? That is, do the grouping on the basis of the key\_field > as usual, but give me the statistics for multiple fields. Is that possible > in the current version (0.16)? > > Thanks, > Hari ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [July 8, 2011, 3:02pm UTC](https://discuss.elastic.co/t/aggregate-multiple-value-fields-separately-in-terms-stats-facet/4816/4 "2011-07-08T15:02:27Z")
ok, might still be missing something, but I still don't see why you can't use several filters (in the same request) under different names (on the second search request). One facet will be on the key field an field2, the second will be on key field and field3. On Friday, July 8, 2011 at 9:47 AM, Hari Shankar wrote: > Yep I thought of that but I would like to maintain order based on one particular field. e.g, I want to top 20 terms based on field 1, but for those 20 terms I want aggregations on field 2 and field 3 also. > > Right now the way I do this is I do the aggregation based on field 1, I set the order to "total", then I have to do a second query with a filter for these terms that were returned in the first query. > > On Fri, Jul 8, 2011 at 10:24 AM, Shay Banon \<[shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com) ([mailto:shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com))\> wrote: > > > You can simply use multiple terms stats facets, one per value field (with the same key field). > > > > On Friday, July 8, 2011 at 7:48 AM, Hari Shankar wrote: > > > > > Hi, > > > > > > I tried giving multiple value\_fields in the terms stats facet. But then it seems to add up all the value fields when calculating the statistics. Can I instead make it compute the stats separately for each field that I specify in the value\_field? That is, do the grouping on the basis of the key\_field as usual, but give me the statistics for multiple fields. Is that possible in the current version (0.16)? > > > > > > Thanks, > > > Hari ---
**Author:** ![Hari\_Shankar](https://avatars.discourse-cdn.com/v4/letter/h/ad7895/32.png) [@Hari\_Shankar](https://discuss.elastic.co/u/Hari_Shankar)\ **Post date:** [July 8, 2011, 4:10pm UTC](https://discuss.elastic.co/t/aggregate-multiple-value-fields-separately-in-terms-stats-facet/4816/5 "2011-07-08T16:10:57Z")
I am unable to maintain order when I use multiple facets. e.g, Lets say I have this data: Customer Clicks Logins C1 3 4 C1 4 4 C1 5 2 C2 2 5 C2 2 8 C2 2 1 Now, let us say I do a terms\_stats facet on this, with key\_field as Customer and value field as Clicks, and order is set to "total". So I will get back this: C1 --\> 12 clicks C2 --\> 6 clicks Now if I add another facet for Logins, if I order by total, I will get C2 --\> 14 logins C1 --\> 10 logins What I would like to do is maintain order between these two. That is, I want data ordered by total clicks: C1 --\> 12 clicks, 10 logins C2 --\> 6 clicks, 14 logins or I could have them ordered by logins, in which case it would be: C2 --\> 14 logins, 6 clicks C1 -0\> 10 logins, 12 clicks Is that what you were thinking? Is that somehow doable currently? On Fri, Jul 8, 2011 at 8:32 PM, Shay Banon [shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com)wrote: > ok, might still be missing something, but I still don't see why you can't > use several filters (in the same request) under different names (on the > second search request). One facet will be on the key field an field2, the > second will be on key field and field3. > > On Friday, July 8, 2011 at 9:47 AM, Hari Shankar wrote: > > Yep I thought of that but I would like to maintain order based on one > particular field. e.g, I want to top 20 terms based on field 1, but for > those 20 terms I want aggregations on field 2 and field 3 also. > > Right now the way I do this is I do the aggregation based on field 1, I set > the order to "total", then I have to do a second query with a filter for > these terms that were returned in the first query. > > On Fri, Jul 8, 2011 at 10:24 AM, Shay Banon [shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com)wrote: > > You can simply use multiple terms stats facets, one per value field (with > the same key field). > > On Friday, July 8, 2011 at 7:48 AM, Hari Shankar wrote: > > Hi, > > I tried giving multiple value\_fields in the terms stats facet. But then it > seems to add up all the value fields when calculating the statistics. Can I > instead make it compute the stats separately for each field that I specify > in the value\_field? That is, do the grouping on the basis of the key\_field > as usual, but give me the statistics for multiple fields. Is that possible > in the current version (0.16)? > > Thanks, > Hari ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [July 8, 2011, 8:58pm UTC](https://discuss.elastic.co/t/aggregate-multiple-value-fields-separately-in-terms-stats-facet/4816/6 "2011-07-08T20:58:29Z")
What you can do in this case is make two search calls. The first, gets Customer and Clicks. The second does a terms stats on Customer and Logins, but has a facet\_filter with terms filter holding the customers you want to get the results back for. If its a different order, first logins and then clicks, you can switch the order. Also, make sure to use search\_type set to count where possible, if you don't need the hits back. On Friday, July 8, 2011 at 7:10 PM, Hari Shankar wrote: > I am unable to maintain order when I use multiple facets. e.g, Lets say I have this data: > Customer Clicks Logins > C1 3 4 > C1 4 4 > C1 5 2 > C2 2 5 > C2 2 8 > C2 2 1 > > Now, let us say I do a terms\_stats facet on this, with key\_field as Customer and value field as Clicks, and order is set to "total". So I will get back this: > C1 --\> 12 clicks > C2 --\> 6 clicks > > Now if I add another facet for Logins, if I order by total, I will get > C2 --\> 14 logins > C1 --\> 10 logins > > What I would like to do is maintain order between these two. That is, I want data ordered by total clicks: > C1 --\> 12 clicks, 10 logins > C2 --\> 6 clicks, 14 logins > > or I could have them ordered by logins, in which case it would be: > C2 --\> 14 logins, 6 clicks > C1 -0\> 10 logins, 12 clicks > > Is that what you were thinking? Is that somehow doable currently? > > On Fri, Jul 8, 2011 at 8:32 PM, Shay Banon \<[shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com) ([mailto:shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com))\> wrote: > > > ok, might still be missing something, but I still don't see why you can't use several filters (in the same request) under different names (on the second search request). One facet will be on the key field an field2, the second will be on key field and field3. > > > > On Friday, July 8, 2011 at 9:47 AM, Hari Shankar wrote: > > > > > Yep I thought of that but I would like to maintain order based on one particular field. e.g, I want to top 20 terms based on field 1, but for those 20 terms I want aggregations on field 2 and field 3 also. > > > > > > Right now the way I do this is I do the aggregation based on field 1, I set the order to "total", then I have to do a second query with a filter for these terms that were returned in the first query. > > > > > > On Fri, Jul 8, 2011 at 10:24 AM, Shay Banon \<[shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com) ([mailto:shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com))\> wrote: > > > > > > > You can simply use multiple terms stats facets, one per value field (with the same key field). > > > > > > > > On Friday, July 8, 2011 at 7:48 AM, Hari Shankar wrote: > > > > > > > > > Hi, > > > > > > > > > > I tried giving multiple value\_fields in the terms stats facet. But then it seems to add up all the value fields when calculating the statistics. Can I instead make it compute the stats separately for each field that I specify in the value\_field? That is, do the grouping on the basis of the key\_field as usual, but give me the statistics for multiple fields. Is that possible in the current version (0.16)? > > > > > > > > > > Thanks, > > > > > Hari ---
**Author:** ![Hari\_Shankar](https://avatars.discourse-cdn.com/v4/letter/h/ad7895/32.png) [@Hari\_Shankar](https://discuss.elastic.co/u/Hari_Shankar)\ **Post date:** [July 9, 2011, 7:10am UTC](https://discuss.elastic.co/t/aggregate-multiple-value-fields-separately-in-terms-stats-facet/4816/7 "2011-07-09T07:10:04Z")
ok, that is the way we are doing it currently, but it would be faster if we do that on es itself right? Thanks for the clarification Shay! On Sat, Jul 9, 2011 at 2:28 AM, Shay Banon [shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com)wrote: > What you can do in this case is make two search calls. The first, gets > Customer and Clicks. The second does a terms stats on Customer and Logins, > but has a facet\_filter with terms filter holding the customers you want to > get the results back for. > > If its a different order, first logins and then clicks, you can switch the > order. > > Also, make sure to use search\_type set to count where possible, if you > don't need the hits back. > > On Friday, July 8, 2011 at 7:10 PM, Hari Shankar wrote: > > I am unable to maintain order when I use multiple facets. e.g, Lets say I > have this data: > Customer Clicks Logins > C1 3 4 > C1 4 4 > C1 5 2 > C2 2 5 > C2 2 8 > C2 2 1 > > Now, let us say I do a terms\_stats facet on this, with key\_field as > Customer and value field as Clicks, and order is set to "total". So I will > get back this: > C1 --\> 12 clicks > C2 --\> 6 clicks > > Now if I add another facet for Logins, if I order by total, I will get > C2 --\> 14 logins > C1 --\> 10 logins > > What I would like to do is maintain order between these two. That is, I > want data ordered by total clicks: > C1 --\> 12 clicks, 10 logins > C2 --\> 6 clicks, 14 logins > > or I could have them ordered by logins, in which case it would be: > C2 --\> 14 logins, 6 clicks > C1 -0\> 10 logins, 12 clicks > > Is that what you were thinking? Is that somehow doable currently? > > On Fri, Jul 8, 2011 at 8:32 PM, Shay Banon [shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com)wrote: > > ok, might still be missing something, but I still don't see why you can't > use several filters (in the same request) under different names (on the > second search request). One facet will be on the key field an field2, the > second will be on key field and field3. > > On Friday, July 8, 2011 at 9:47 AM, Hari Shankar wrote: > > Yep I thought of that but I would like to maintain order based on one > particular field. e.g, I want to top 20 terms based on field 1, but for > those 20 terms I want aggregations on field 2 and field 3 also. > > Right now the way I do this is I do the aggregation based on field 1, I set > the order to "total", then I have to do a second query with a filter for > these terms that were returned in the first query. > > On Fri, Jul 8, 2011 at 10:24 AM, Shay Banon [shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com)wrote: > > You can simply use multiple terms stats facets, one per value field (with > the same key field). > > On Friday, July 8, 2011 at 7:48 AM, Hari Shankar wrote: > > Hi, > > I tried giving multiple value\_fields in the terms stats facet. But then it > seems to add up all the value fields when calculating the statistics. Can I > instead make it compute the stats separately for each field that I specify > in the value\_field? That is, do the grouping on the basis of the key\_field > as usual, but give me the statistics for multiple fields. Is that possible > in the current version (0.16)? > > Thanks, > Hari ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [July 10, 2011, 12:32am UTC](https://discuss.elastic.co/t/aggregate-multiple-value-fields-separately-in-terms-stats-facet/4816/8 "2011-07-10T00:32:15Z")
Actually, it depends on the type of data. It might be faster to do it by executing the query twice, and in other conditions, it might be faster to have a compound facet implementation for that in ES (which we don't have currently). On Saturday, July 9, 2011 at 10:10 AM, Hari Shankar wrote: > ok, that is the way we are doing it currently, but it would be faster if we do that on es itself right? Thanks for the clarification Shay! > > On Sat, Jul 9, 2011 at 2:28 AM, Shay Banon \<[shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com) ([mailto:shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com))\> wrote: > > > What you can do in this case is make two search calls. The first, gets Customer and Clicks. The second does a terms stats on Customer and Logins, but has a facet\_filter with terms filter holding the customers you want to get the results back for. > > > > If its a different order, first logins and then clicks, you can switch the order. > > > > Also, make sure to use search\_type set to count where possible, if you don't need the hits back. > > > > On Friday, July 8, 2011 at 7:10 PM, Hari Shankar wrote: > > > > > I am unable to maintain order when I use multiple facets. e.g, Lets say I have this data: > > > Customer Clicks Logins > > > C1 3 4 > > > C1 4 4 > > > C1 5 2 > > > C2 2 5 > > > C2 2 8 > > > C2 2 1 > > > > > > Now, let us say I do a terms\_stats facet on this, with key\_field as Customer and value field as Clicks, and order is set to "total". So I will get back this: > > > C1 --\> 12 clicks > > > C2 --\> 6 clicks > > > > > > Now if I add another facet for Logins, if I order by total, I will get > > > C2 --\> 14 logins > > > C1 --\> 10 logins > > > > > > What I would like to do is maintain order between these two. That is, I want data ordered by total clicks: > > > C1 --\> 12 clicks, 10 logins > > > C2 --\> 6 clicks, 14 logins > > > > > > or I could have them ordered by logins, in which case it would be: > > > C2 --\> 14 logins, 6 clicks > > > C1 -0\> 10 logins, 12 clicks > > > > > > Is that what you were thinking? Is that somehow doable currently? > > > > > > On Fri, Jul 8, 2011 at 8:32 PM, Shay Banon \<[shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com) ([mailto:shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com))\> wrote: > > > > > > > ok, might still be missing something, but I still don't see why you can't use several filters (in the same request) under different names (on the second search request). One facet will be on the key field an field2, the second will be on key field and field3. > > > > > > > > On Friday, July 8, 2011 at 9:47 AM, Hari Shankar wrote: > > > > > > > > > Yep I thought of that but I would like to maintain order based on one particular field. e.g, I want to top 20 terms based on field 1, but for those 20 terms I want aggregations on field 2 and field 3 also. > > > > > > > > > > Right now the way I do this is I do the aggregation based on field 1, I set the order to "total", then I have to do a second query with a filter for these terms that were returned in the first query. > > > > > > > > > > On Fri, Jul 8, 2011 at 10:24 AM, Shay Banon \<[shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com) ([mailto:shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com))\> wrote: > > > > > > > > > > > You can simply use multiple terms stats facets, one per value field (with the same key field). > > > > > > > > > > > > On Friday, July 8, 2011 at 7:48 AM, Hari Shankar wrote: > > > > > > > > > > > > > Hi, > > > > > > > > > > > > > > I tried giving multiple value\_fields in the terms stats facet. But then it seems to add up all the value fields when calculating the statistics. Can I instead make it compute the stats separately for each field that I specify in the value\_field? That is, do the grouping on the basis of the key\_field as usual, but give me the statistics for multiple fields. Is that possible in the current version (0.16)? > > > > > > > > > > > > > > Thanks, > > > > > > > Hari ---
**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, 4:01am UTC](https://discuss.elastic.co/t/aggregate-multiple-value-fields-separately-in-terms-stats-facet/4816/9 "2017-07-06T04:01:07Z")