# Terms stats facet on multiple fields? **URL:** \ **Category:** Elasticsearch\ **Created:** [June 27, 2011, 8:00pm UTC](https://discuss.elastic.co/t/terms-stats-facet-on-multiple-fields/4720 "2011-06-27T20:00:21Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![searchersteve](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/searchersteve/32/3008_2.png) [@searchersteve](https://discuss.elastic.co/u/searchersteve)\ **Post date:** [June 27, 2011, 8:00pm UTC](https://discuss.elastic.co/t/terms-stats-facet-on-multiple-fields/4720/1 "2011-06-27T20:00:21Z")
If I understand the [documentation](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet.html) on the Terms Stats Facet, I can create sums, counts and averages of a value given in one field, aggregated by all values of another field. It's a way of creating statistical cross-tabs. Does this feature work on more than one aggregation field? In other words, if I had a data set of: Building type, year built, area. Could I somehow calculate average area, by building type, by year? Building type Year built Avg. area Residential 2004 3500 Residential 2005 3600 Residential 2006 3700 Residential 2007 3800 Residential 2008 3700 Residential 2009 3650 Commerical 2004 14,000 Commercial 2005 15,000 etc.... My guess is that Shay would have listed the syntax if this were already do-able. So, if it's not there, please consider this a humble feature request. Thanks in advance. (I should say, also, thank you for introducing the term-stat facet in the first place! It's a powerful tool.) Steve ---
**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:** [June 27, 2011, 9:40pm UTC](https://discuss.elastic.co/t/terms-stats-facet-on-multiple-fields/4720/2 "2011-06-27T21:40:54Z")
Hi, The terms stats only supports "two levels", the terms, and then the field to do the stats on. The whole question of starting to support multiple "levels" becomes very interesting, but as you can see, the options are basically limitless (histogram -\> terms -\> stats, or terms-\>terms-\>histogram-\>stats). It even gets more interesting to try and support pivot table like stats (and I am taking about true pivot table here...). Its not something terribly difficult to do, requires a lot of implementation to be done in an optimized manner (specific handling for data types), and figuring out how to represent a pivot table in the most optimized manner in the response. I do want to try and tackle pivot table faceting support. But a bit more down the road from now. -shay.banon On Monday, June 27, 2011 at 11:00 PM, searchersteve wrote: > If I understand the > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet.html) > documentation on the Terms Stats Facet, I can create sums, counts and > averages of a value given in one field, aggregated by all values of another > field. It's a way of creating statistical cross-tabs. > > Does this feature work on more than one aggregation field? In other words, > if I had a data set of: Building type, year built, area. Could I somehow > calculate average area, by building type, by year? > > Building type Year built Avg. area > Residential 2004 3500 > Residential 2005 3600 > Residential 2006 3700 > Residential 2007 3800 > Residential 2008 3700 > Residential 2009 3650 > Commerical 2004 14,000 > Commercial 2005 15,000 > > etc.... > > My guess is that Shay would have listed the syntax if this were already > do-able. So, if it's not there, please consider this a humble feature > request. Thanks in advance. (I should say, also, thank you for introducing > the term-stat facet in the first place! It's a powerful tool.) > > Steve > > -- > View this message in context: [http://elasticsearch-users.115913.n3.nabble.com/Terms-stats-facet-on-multiple-fields-tp3115086p3115086.html](http://elasticsearch-users.115913.n3.nabble.com/Terms-stats-facet-on-multiple-fields-tp3115086p3115086.html) > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com) ([http://Nabble.com](http://Nabble.com)). ---
**Author:** ![searchersteve](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/searchersteve/32/3008_2.png) [@searchersteve](https://discuss.elastic.co/u/searchersteve)\ **Post date:** [June 30, 2011, 1:13am UTC](https://discuss.elastic.co/t/terms-stats-facet-on-multiple-fields/4720/3 "2011-06-30T01:13:13Z")
Ahh, thanks much. Good to hear you're thinking about it for the future. Best, Steve Hi, The terms stats only supports "two levels", the terms, and then the field to do the stats on. The whole question of starting to support multiple "levels" becomes very interesting, but as you can see, the options are basically limitless (histogram -\> terms -\> stats, or terms-\>terms-\>histogram-\>stats). It even gets more interesting to try and support pivot table like stats (and I am taking about true pivot table here...). Its not something terribly difficult to do, requires a lot of implementation to be done in an optimized manner (specific handling for data types), and figuring out how to represent a pivot table in the most optimized manner in the response. I do want to try and tackle pivot table faceting support. But a bit more down the road from now. -shay.banon On Monday, June 27, 2011 at 11:00 PM, searchersteve wrote: > If I understand the > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet.html) > documentation on the Terms Stats Facet, I can create sums, counts and > averages of a value given in one field, aggregated by all values of another > field. It's a way of creating statistical cross-tabs. > > Does this feature work on more than one aggregation field? In other words, > if I had a data set of: Building type, year built, area. Could I somehow > calculate average area, by building type, by year? > > Building type Year built Avg. area > Residential 2004 3500 > Residential 2005 3600 > Residential 2006 3700 > Residential 2007 3800 > Residential 2008 3700 > Residential 2009 3650 > Commerical 2004 14,000 > Commercial 2005 15,000 > > etc.... > > My guess is that Shay would have listed the syntax if this were already > do-able. So, if it's not there, please consider this a humble feature > request. Thanks in advance. (I should say, also, thank you for introducing > the term-stat facet in the first place! It's a powerful tool.) > > Steve > > -- > View this message in context: [http://elasticsearch-users.115913.n3.nabble.com/Terms-stats-facet-on-multiple-fields-tp3115086p3115086.html](http://elasticsearch-users.115913.n3.nabble.com/Terms-stats-facet-on-multiple-fields-tp3115086p3115086.html) > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com) ([http://Nabble.com](http://Nabble.com)). ---
**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:02am UTC](https://discuss.elastic.co/t/terms-stats-facet-on-multiple-fields/4720/4 "2017-07-06T04:02:13Z")