# Elasticsearch division with statistical facets
**URL:** https://discuss.elastic.co/t/elasticsearch-division-with-statistical-facets/10450
**Category:** Elasticsearch
**Created:** [January 22, 2013, 12:11pm UTC](https://discuss.elastic.co/t/elasticsearch-division-with-statistical-facets/10450 "2013-01-22T12:11:15Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@Ahmet\_DAL](https://discuss.elastic.co/u/Ahmet_DAL)
#### Post date: [January 22, 2013, 12:11pm UTC](https://discuss.elastic.co/t/elasticsearch-division-with-statistical-facets/10450/1 "2013-01-22T12:11:15Z")
I need to take divisions of two field. I tried it with statistical facets
with scripting. But it gives me NaN values. The query is like,
{
"size": 0,
"query": {
"match\_all": {}
},
"facets": {
"stat1": {
"statistical": {
"script": "(doc['COUNT\_int'].value/ doc['TOTAL\_COUNT\_int'].value)"
}
}
}
}
and the response ;
{
- took: 38
- timed\_out: false
- \_shards: {
- total: 5
- successful: 5
- failed: 0
}
- hits: {
- total: 331324
- max\_score: 1
- hits: []
}
- facets: {
- stat1: {
- \_type: statistical
- count: 331324
- total: NaN
- min: 0
- max: Infinity
- mean: NaN
- sum\_of\_squares: NaN
- variance: NaN
- std\_deviation: NaN
}
}
}
I tried it with doubleValue too but the result was same.
Note: TOTAL\_COUNT\_int greater than COUNT\_int
Why didn't it take this divison ?
Thank You!
--
---
### Author:  [@Ivan](https://discuss.elastic.co/u/Ivan)
#### Post date: [January 22, 2013, 4:08pm UTC](https://discuss.elastic.co/t/elasticsearch-division-with-statistical-facets/10450/2 "2013-01-22T16:08:37Z")
I would assume the issue is that you did a divide by zero. Is it possible
that TOTAL\_COUNT was not indexed? Does it work using the source?
--
Ivan
On Tue, Jan 22, 2013 at 4:11 AM, Whispered [ceahmetdal@gmail.com](mailto:ceahmetdal@gmail.com) wrote:
> I need to take divisions of two field. I tried it with statistical facets
> with scripting. But it gives me NaN values. The query is like,
> {
> "size": 0,
> "query": {
> "match\_all": {}
> },
> "facets": {
> "stat1": {
> "statistical": {
> "script": "(doc['COUNT\_int'].value/ doc['TOTAL\_COUNT\_int'].value)"
> }
> }
> }
> }
>
> and the response ;
>
> {
>
> - took: 38
> - timed\_out: false
> - \_shards: {
> - total: 5
> - successful: 5
> - failed: 0
> }
>
> - hits: {
> - total: 331324
> - max\_score: 1
> - hits:
> }
>
> - facets: {
> - stat1: {
> - \_type: statistical
> - count: 331324
> - total: NaN
> - min: 0
> - max: Infinity
> - mean: NaN
> - sum\_of\_squares: NaN
> - variance: NaN
> - std\_deviation: NaN
> }
> }
>
> }
> I tried it with doubleValue too but the result was same.
>
> Note: TOTAL\_COUNT\_int greater than COUNT\_int
>
> Why didn't it take this divison ?
>
> Thank You!
>
> --
--
---
### Author:  [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)
#### Post date: [January 22, 2013, 4:28pm UTC](https://discuss.elastic.co/t/elasticsearch-division-with-statistical-facets/10450/3 "2013-01-22T16:28:50Z")
On Tue, 2013-01-22 at 04:11 -0800, Whispered wrote:
> I need to take divisions of two field. I tried it with statistical
> facets with scripting. But it gives me NaN values. The query is like,
How are you sending your query? via curl?
In which case, make sure that your single quotes are not being messed up
by shell escaping.
Replace the ' in your script with \0027 (single quote in UTF8)
clint
> {
> "size": 0,
> "query": {
> "match\_all": {}
> },
> "facets": {
> "stat1": {
> "statistical": {
> "script": "(doc['COUNT\_int'].value/
> doc['TOTAL\_COUNT\_int'].value)"
> }
> }
> }
> }
>
> and the response ;
>
> {
> \* took: 38
> \* timed\_out: false
> \* \_shards: {
> \* total: 5
> \* successful: 5
> \* failed: 0
> }
> \* hits: {
> \* total: 331324
> \* max\_score: 1
> \* hits:
> }
> \* facets: {
> \* stat1: {
> \* \_type: statistical
> \* count: 331324
> \* total: NaN
> \* min: 0
> \* max: Infinity
> \* mean: NaN
> \* sum\_of\_squares: NaN
> \* variance: NaN
> \* std\_deviation: NaN
> }
> }
> }
>
> I tried it with doubleValue too but the result was same.
>
> Note: TOTAL\_COUNT\_int greater than COUNT\_int
>
> Why didn't it take this divison ?
>
> Thank You!
>
> --
--
---
### Author:  [@Ahmet\_DAL](https://discuss.elastic.co/u/Ahmet_DAL)
#### Post date: [January 23, 2013, 11:19am UTC](https://discuss.elastic.co/t/elasticsearch-division-with-statistical-facets/10450/4 "2013-01-23T11:19:01Z")
I'm sending the query with \_head plugin user interface.
22 Ocak 2013 Salı 18:28:50 UTC+2 tarihinde Clinton Gormley yazdı:
> On Tue, 2013-01-22 at 04:11 -0800, Whispered wrote:
>
> > I need to take divisions of two field. I tried it with statistical
> > facets with scripting. But it gives me NaN values. The query is like,
>
> How are you sending your query? via curl?
>
> In which case, make sure that your single quotes are not being messed up
> by shell escaping.
>
> Replace the ' in your script with \0027 (single quote in UTF8)
>
> clint
>
> > {
> > "size": 0,
> > "query": {
> > "match\_all": {}
> > },
> > "facets": {
> > "stat1": {
> > "statistical": {
> > "script": "(doc['COUNT\_int'].value/
> > doc['TOTAL\_COUNT\_int'].value)"
> > }
> > }
> > }
> > }
> >
> > and the response ;
> >
> > {
> > \* took: 38
> > \* timed\_out: false
> > \* \_shards: {
> > \* total: 5
> > \* successful: 5
> > \* failed: 0
> > }
> > \* hits: {
> > \* total: 331324
> > \* max\_score: 1
> > \* hits:
> > }
> > \* facets: {
> > \* stat1: {
> > \* \_type: statistical
> > \* count: 331324
> > \* total: NaN
> > \* min: 0
> > \* max: Infinity
> > \* mean: NaN
> > \* sum\_of\_squares: NaN
> > \* variance: NaN
> > \* std\_deviation: NaN
> > }
> > }
> > }
> >
> > I tried it with doubleValue too but the result was same.
> >
> > Note: TOTAL\_COUNT\_int greater than COUNT\_int
> >
> > Why didn't it take this divison ?
> >
> > Thank You!
> >
> > --
--
---
### Author:  [@Ahmet\_DAL](https://discuss.elastic.co/u/Ahmet_DAL)
#### Post date: [January 23, 2013, 11:22am UTC](https://discuss.elastic.co/t/elasticsearch-division-with-statistical-facets/10450/5 "2013-01-23T11:22:01Z")
{
"size": 0,
"query": {
"match\_all": {}
},
"facets": {
"stat1": {
"statistical": {
"script": "doc['OPC\_int'].value"
}
}
}
}
gives;
{
- took: 36
- timed\_out: false
- \_shards: {
- total: 5
- successful: 5
- failed: 0
}
- hits: {
- total: 240360
- max\_score: 1
- hits:
}
- facets: {
- stat1: {
- \_type: statistical
- count: 240360
- total: 5416673
- min: 0
- max: 96
- mean: 22.53566733233483
- sum\_of\_squares: 212503663
- variance: 376.249468397237
- std\_deviation: 19.397151038161173
}
}
}
and the other query of the seperated version is
{
"size": 0,
"query": {
"match\_all": {}
},
"facets": {
"stat1": {
"statistical": {
"script": "doc['TOTAL\_COUNT\_int'].value"
}
}
}
}
gives;
{
- took: 19
- timed\_out: false
- \_shards: {
- total: 5
- successful: 5
- failed: 0
}
- hits: {
- total: 240360
- max\_score: 1
- hits:
}
- facets: {
- stat1: {
- \_type: statistical
- count: 240360
- total: 8672000
- min: 0
- max: 64
- mean: 36.079214511565986
- sum\_of\_squares: 410605440
- variance: 406.58383988891586
- std\_deviation: 20.163924218487725
}
}
}
AS you can see they are not zero.
22 Ocak 2013 Salı 18:08:37 UTC+2 tarihinde Ivan Brusic yazdı:
> I would assume the issue is that you did a divide by zero. Is it possible
> that TOTAL\_COUNT was not indexed? Does it work using the source?
>
> --
> Ivan
>
> On Tue, Jan 22, 2013 at 4:11 AM, Whispered \<[ceahm...@gmail.com](mailto:ceahm...@gmail.com)\
>
> > wrote:
>
> > I need to take divisions of two field. I tried it with statistical facets
> > with scripting. But it gives me NaN values. The query is like,
> > {
> > "size": 0,
> > "query": {
> > "match\_all": {}
> > },
> > "facets": {
> > "stat1": {
> > "statistical": {
> > "script": "(doc['COUNT\_int'].value/
> > doc['TOTAL\_COUNT\_int'].value)"
> > }
> > }
> > }
> > }
> >
> > and the response ;
> >
> > {
> >
> > - took: 38
> > - timed\_out: false
> > - \_shards: {
> > - total: 5
> > - successful: 5
> > - failed: 0
> > }
> >
> > - hits: {
> > - total: 331324
> > - max\_score: 1
> > - hits:
> > }
> >
> > - facets: {
> > - stat1: {
> > - \_type: statistical
> > - count: 331324
> > - total: NaN
> > - min: 0
> > - max: Infinity
> > - mean: NaN
> > - sum\_of\_squares: NaN
> > - variance: NaN
> > - std\_deviation: NaN
> > }
> > }
> >
> > }
> > I tried it with doubleValue too but the result was same.
> >
> > Note: TOTAL\_COUNT\_int greater than COUNT\_int
> >
> > Why didn't it take this divison ?
> >
> > Thank You!
> >
> > --
--
---
### Author:  [@Ivan](https://discuss.elastic.co/u/Ivan)
#### Post date: [January 23, 2013, 4:01pm UTC](https://discuss.elastic.co/t/elasticsearch-division-with-statistical-facets/10450/6 "2013-01-23T16:01:53Z")
On Wed, Jan 23, 2013 at 3:22 AM, Whispered [ceahmetdal@gmail.com](mailto:ceahmetdal@gmail.com) wrote:
> AS you can see they are not zero.
The second faceted query using TOTAL\_COUNT shows that the minimum value is
in fact 0. For at least one document, there is a division by zero error.
Can you query for "TOTAL\_COUNT\_int = 0" and see what the results are?
--
Ivan
--
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 2:55am UTC](https://discuss.elastic.co/t/elasticsearch-division-with-statistical-facets/10450/7 "2017-07-06T02:55:00Z")