# Question about the term\_stats facet
**URL:** https://discuss.elastic.co/t/question-about-the-term-stats-facet/12454
**Category:** Elasticsearch
**Created:** [June 18, 2013, 7:47am UTC](https://discuss.elastic.co/t/question-about-the-term-stats-facet/12454 "2013-06-18T07:47:25Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Ben\_2](https://discuss.elastic.co/u/Ben_2)
#### Post date: [June 18, 2013, 7:47am UTC](https://discuss.elastic.co/t/question-about-the-term-stats-facet/12454/1 "2013-06-18T07:47:25Z")
I am getting started with ES and have a hard time understanding how to
build a query in ES to get a result set that is similar to an SQL result
set, i.e. get more fields from my documents within the facet result, like a
grid, or table, which I can then loop over in the view.
My data is similar to this:
```
{ order_placed: "2013-05-13 16:43",
product_id: 100,
sales_qty: 2,
payment_method: "creditcard",
country: "US",
retailer: "ABC Store"
},
{ order_placed: "2013-05-14 11:24",
product_id: 203,
sales_qty: 1,
payment_method: "cash",
country: "DE",
retailer: "XYZ Store"
},
{ order_placed: "2013-05-14 18:10",
product_id: 138,
sales_qty: 4,
payment_method: "unknown",
country: "JP",
retailer: "NPN Store"
} ... etc.
```
How do I do this in ES?
SELECT sum(sales\_qty),country
FROM sales
WHERE product\_id = 183 AND retailer = 'NPN Store'
GROUP BY country
ORDER BY sum(sales\_qty) DESC;
The sum(sales\_qty) should be limited to the product\_id 183. I think what I
need is the term\_stats facet, sort by reverse-max, and facet filter by
product\_id = 183 and retailer = 'NPN Store' - I'm not sure how to put it
all together.
Basically I want to use ES like a data cube, if that makes any sense.
Pointers to documentation would be fine, as I am currently not sure what to
look for.
--
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:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [June 18, 2013, 8:06am UTC](https://discuss.elastic.co/t/question-about-the-term-stats-facet/12454/2 "2013-06-18T08:06:23Z")
Somehow, you want to compute on orders. So basically, you have to index orders in separate documents.
Then run a Query, using Query DSL, filtering by retailer and product\_id using a BoolFilter (you will need not to analyze retailer field, set index to not\_analyzed).
Add a Terms Stats facet on field country for the key\_field and sales\_qty for the value\_field.
Does it help?
--
David Pilato | Technical Advocate | [Elasticsearch.com](http://Elasticsearch.com)
@dadoonet | @elasticsearchfr | @scrutmydocs
Le 18 juin 2013 à 09:47, Ben [tonkatsufan@gmail.com](mailto:tonkatsufan@gmail.com) a écrit :
> I am getting started with ES and have a hard time understanding how to build a query in ES to get a result set that is similar to an SQL result set, i.e. get more fields from my documents within the facet result, like a grid, or table, which I can then loop over in the view.
>
> My data is similar to this:
>
> ```
> { order_placed: "2013-05-13 16:43",
> product_id: 100,
> sales_qty: 2,
> payment_method: "creditcard",
> country: "US",
> retailer: "ABC Store"
> },
> { order_placed: "2013-05-14 11:24",
> product_id: 203,
> sales_qty: 1,
> payment_method: "cash",
> country: "DE",
> retailer: "XYZ Store"
> },
> { order_placed: "2013-05-14 18:10",
> product_id: 138,
> sales_qty: 4,
> payment_method: "unknown",
> country: "JP",
> retailer: "NPN Store"
> } ... etc.
>
> ```
>
> How do I do this in ES?
>
> SELECT sum(sales\_qty),country
> FROM sales
> WHERE product\_id = 183 AND retailer = 'NPN Store'
> GROUP BY country
> ORDER BY sum(sales\_qty) DESC;
> The sum(sales\_qty) should be limited to the product\_id 183. I think what I need is the term\_stats facet, sort by reverse-max, and facet filter by product\_id = 183 and retailer = 'NPN Store' - I'm not sure how to put it all together.
>
> Basically I want to use ES like a data cube, if that makes any sense.
>
> Pointers to documentation would be fine, as I am currently not sure what to look for.
>
> --
> 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:  [@Ben\_2](https://discuss.elastic.co/u/Ben_2)
#### Post date: [June 18, 2013, 8:12am UTC](https://discuss.elastic.co/t/question-about-the-term-stats-facet/12454/3 "2013-06-18T08:12:57Z")
Hello David,
yes it should do it, but if I use query filters, it would not change the
SUM() calculation because the facet results are calculated from all
documents - at least that's how I understand the documentation.
Would I need to use facet filters to limit the statistical calculations
like SUM() to the filtered result set?
Thank you
Ben
On Tuesday, June 18, 2013 5:06:23 PM UTC+9, David Pilato wrote:
> Somehow, you want to compute on orders. So basically, you have to index
> orders in separate documents.
> Then run a Query, using Query DSL, filtering by retailer and product\_id
> using a BoolFilter[http://www.elasticsearch.org/guide/reference/query-dsl/bool-filter/](http://www.elasticsearch.org/guide/reference/query-dsl/bool-filter/) (you
> will need not to analyze retailer field, set index to not\_analyzed[http://www.elasticsearch.org/guide/reference/mapping/core-types/](http://www.elasticsearch.org/guide/reference/mapping/core-types/)
> ).
>
> Add a Terms Stats facet[http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet/](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet/) on
> field country for the key\_field and sales\_qty for the value\_field.
>
> Does it help?
>
> --
> _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)_
> @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr[https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr)
> | @scrutmydocs [https://twitter.com/scrutmydocs](https://twitter.com/scrutmydocs)
>
> Le 18 juin 2013 à 09:47, Ben \<[tonka...@gmail.com](mailto:tonka...@gmail.com) \\> a écrit :
>
> I am getting started with ES and have a hard time understanding how to
> build a query in ES to get a result set that is similar to an SQL result
> set, i.e. get more fields from my documents within the facet result, like a
> grid, or table, which I can then loop over in the view.
>
> My data is similar to this:
>
> ```
> { order_placed: "2013-05-13 16:43",
> product_id: 100,
> sales_qty: 2,
> payment_method: "creditcard",
> country: "US",
> retailer: "ABC Store"
> },
> { order_placed: "2013-05-14 11:24",
> product_id: 203,
> sales_qty: 1,
> payment_method: "cash",
> country: "DE",
> retailer: "XYZ Store"
> },
> { order_placed: "2013-05-14 18:10",
> product_id: 138,
> sales_qty: 4,
> payment_method: "unknown",
> country: "JP",
> retailer: "NPN Store"
> } ... etc.
>
> ```
>
> How do I do this in ES?
>
> SELECT sum(sales\_qty),country
> FROM sales
> WHERE product\_id = 183 AND retailer = 'NPN Store'
> GROUP BY country
> ORDER BY sum(sales\_qty) DESC;
>
> The sum(sales\_qty) should be limited to the product\_id 183. I think what I
> need is the term\_stats facet, sort by reverse-max, and facet filter by
> product\_id = 183 and retailer = 'NPN Store' - I'm not sure how to put it
> all together.
>
> Basically I want to use ES like a data cube, if that makes any sense.
>
> Pointers to documentation would be fine, as I am currently not sure what
> to look for.
>
> --
> 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:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [June 18, 2013, 12:00pm UTC](https://discuss.elastic.co/t/question-about-the-term-stats-facet/12454/4 "2013-06-18T12:00:49Z")
Yes!
Or use [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/constant-score-query/).
Should work.
--
David 😉
Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs
Le 18 juin 2013 à 10:12, Ben [tonkatsufan@gmail.com](mailto:tonkatsufan@gmail.com) a écrit :
Hello David,
yes it should do it, but if I use query filters, it would not change the SUM() calculation because the facet results are calculated from all documents - at least that's how I understand the documentation.
Would I need to use facet filters to limit the statistical calculations like SUM() to the filtered result set?
Thank you
Ben
On Tuesday, June 18, 2013 5:06:23 PM UTC+9, David Pilato wrote:
> Somehow, you want to compute on orders. So basically, you have to index orders in separate documents.
> Then run a Query, using Query DSL, filtering by retailer and product\_id using a BoolFilter (you will need not to analyze retailer field, set index to not\_analyzed).
>
> Add a Terms Stats facet on field country for the key\_field and sales\_qty for the value\_field.
>
> Does it help?
>
> --
> David Pilato | Technical Advocate | [Elasticsearch.com](http://Elasticsearch.com)
> @dadoonet | @elasticsearchfr | @scrutmydocs
>
> Le 18 juin 2013 à 09:47, Ben [tonka...@gmail.com](mailto:tonka...@gmail.com) a écrit :
>
> > I am getting started with ES and have a hard time understanding how to build a query in ES to get a result set that is similar to an SQL result set, i.e. get more fields from my documents within the facet result, like a grid, or table, which I can then loop over in the view.
> >
> > My data is similar to this:
> >
> > ```
> > { order_placed: "2013-05-13 16:43",
> > product_id: 100,
> > sales_qty: 2,
> > payment_method: "creditcard",
> > country: "US",
> > retailer: "ABC Store"
> > },
> > { order_placed: "2013-05-14 11:24",
> > product_id: 203,
> > sales_qty: 1,
> > payment_method: "cash",
> > country: "DE",
> > retailer: "XYZ Store"
> > },
> > { order_placed: "2013-05-14 18:10",
> > product_id: 138,
> > sales_qty: 4,
> > payment_method: "unknown",
> > country: "JP",
> > retailer: "NPN Store"
> > } ... etc.
> >
> > ```
> >
> > How do I do this in ES?
> >
> > SELECT sum(sales\_qty),country
> > FROM sales
> > WHERE product\_id = 183 AND retailer = 'NPN Store'
> > GROUP BY country
> > ORDER BY sum(sales\_qty) DESC;
> > The sum(sales\_qty) should be limited to the product\_id 183. I think what I need is the term\_stats facet, sort by reverse-max, and facet filter by product\_id = 183 and retailer = 'NPN Store' - I'm not sure how to put it all together.
> >
> > Basically I want to use ES like a data cube, if that makes any sense.
> >
> > Pointers to documentation would be fine, as I am currently not sure what to look for.
> >
> > --
> > 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).
--
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://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 2:30am UTC](https://discuss.elastic.co/t/question-about-the-term-stats-facet/12454/5 "2017-07-06T02:30:37Z")