# Scripted Field Unique Count of Sum Value

**URL:** <https://discuss.elastic.co/t/scripted-field-unique-count-of-sum-value/254215>\
**Category:** Elasticsearch\
**Created:** [November 4, 2020, 7:13am UTC](https://discuss.elastic.co/t/scripted-field-unique-count-of-sum-value/254215 "2020-11-04T07:13:14Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![111207](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/111207/32/53801_2.png) [@111207](https://discuss.elastic.co/u/111207)\
**Post date:** [November 4, 2020, 7:13am UTC](https://discuss.elastic.co/t/scripted-field-unique-count-of-sum-value/254215/1 "2020-11-04T07:13:14Z")

</div>

I need to create visualization metric for count of ID of value is 0  
I have imported data to elasticsearch And the data in each ID has multiple lines, I would like to sum value by ID. Then Unique Count is a number whose sum is 0.  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/1/b/1b77637037ce6f3f64dd43766fa96b03ea611923.png)

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [November 4, 2020, 7:26am UTC](https://discuss.elastic.co/t/scripted-field-unique-count-of-sum-value/254215/2 "2020-11-04T07:26:25Z")

</div>

```
GET aggs_test/_search
{
  "size": 0,
  "aggs": {
    "ids": {
      "terms": {
        "field": "id.keyword"
      },
      "aggs": {
        "the_sum": {
          "sum": {
            "field": "value"
          }
        },
        "the_script_bucket": {
          "bucket_selector": {
            "buckets_path": {
              "the_sum": "the_sum.value"
            },
            "script": "params.the_sum == 0"
          }
        }
      }
    },
    "the_count_of_bucket": {
      "stats_bucket": {
        "buckets_path": "ids>the_sum"
      }
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![111207](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/111207/32/53801_2.png) [@111207](https://discuss.elastic.co/u/111207)\
**Post date:** [November 4, 2020, 8:15am UTC](https://discuss.elastic.co/t/scripted-field-unique-count-of-sum-value/254215/3 "2020-11-04T08:15:24Z")

</div>

Hi Wangqinghuan  
I'm tried. It not work for me

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [November 4, 2020, 11:27am UTC](https://discuss.elastic.co/t/scripted-field-unique-count-of-sum-value/254215/4 "2020-11-04T11:27:38Z")

</div>

Hi , I post script again which wil get the count of id whose sum is 0.

---

<div class="post-metadata">

**Author:** ![111207](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/111207/32/53801_2.png) [@111207](https://discuss.elastic.co/u/111207)\
**Post date:** [November 5, 2020, 2:59am UTC](https://discuss.elastic.co/t/scripted-field-unique-count-of-sum-value/254215/5 "2020-11-05T02:59:03Z")

</div>

Hi, I'm tried to use your new script but It not work for me  
The value is incorrect

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/8/b857fa46f489784c9214b77c34bdadb17a8005fc.png)

My field name  
"Card Serial.keyword" is an "id"  
"Duration" is a "Score"

After runing your script It Show value of sum only = 253. The Score is mistake  
My Score 'Unique Count Card Serial of sum Duration == 0' is acctually value = 5

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [November 5, 2020, 3:03am UTC](https://discuss.elastic.co/t/scripted-field-unique-count-of-sum-value/254215/6 "2020-11-05T03:03:48Z")

</div>

> [@111207](#):
>
> value of sum only = 253.

Could you post your data file? I'll try it.

---

<div class="post-metadata">

**Author:** ![111207](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/111207/32/53801_2.png) [@111207](https://discuss.elastic.co/u/111207)\
**Post date:** [November 5, 2020, 8:07am UTC](https://discuss.elastic.co/t/scripted-field-unique-count-of-sum-value/254215/7 "2020-11-05T08:07:11Z")

</div>

Card Serial,Duration  
e30-719-40337,7  
e10-619-40215,0  
e10-319-40325,0  
e10-619-40269,6  
e30-719-40285,0  
e30-719-40485,14  
e10-619-40240,0  
e10-319-40325,9  
e10-619-40269,0  
e10-619-40268,17  
e10-619-40299,0  
e10-619-40278,0  
e10-b18-40902,0  
e10-619-40240,0  
e10-619-40188,10  
e10-619-40308,0  
e10-619-40299,0  
e10-619-40279,0  
e10-619-40299,0  
e10-619-40124,0  
e10-619-40226,0  
e30-719-40374,2  
e10-b18-40928,0  
e10-619-40279,0  
e10-619-40229,0  
e10-619-40170,0  
e10-619-40308,0  
e30-719-40270,0  
e10-619-40215,0  
e10-619-40178,3  
e10-619-40203,7  
e30-719-40456,9  
e10-b18-40912,0  
e10-619-40294,5  
e10-b18-40928,0  
e30-719-40288,11  
e10-619-40175,0  
e10-619-40206,0  
e30-719-40513,0  
e10-619-40226,0  
e10-619-40280,0  
e10-619-40174,0  
e10-619-40185,13  
e10-619-40223,0  
e10-619-40133,11  
e10-b18-40902,0  
e10-619-40271,0  
e10-b18-40931,0  
e10-619-40130,0  
e10-619-40308,0  
e10-b18-40902,7  
e30-719-40457,10  
e10-619-40271,0  
e30-719-40267,0  
e10-619-40185,0  
e10-619-40215,0  
e10-619-40185,0  
e30-719-40518,9  
e30-719-40296,10  
e10-619-40308,17  
e10-619-40240,0  
e10-619-40273,10  
e10-619-40130,0  
e10-619-40207,0  
e10-619-40130,0  
e10-619-40215,0  
e10-619-40126,1  
e10-619-40215,13  
e30-719-40404,3  
e10-619-40226,14  
e10-319-40297,0  
e10-619-40279,0  
e10-619-40269,0  
e10-619-40185,0  
e10-619-40174,14  
e10-619-40124,0  
e10-619-40139,0  
e10-619-40223,0  
e10-619-40243,0  
e30-719-40513,0  
e10-619-40215,0  
e30-719-40652,10  
e10-619-40223,0  
e30-719-40513,0  
e10-619-40185,0  
e10-619-40250,10  
e10-619-40139,0  
e10-619-40124,0  
e30-719-40425,16  
e10-619-40280,0  
e10-619-40278,0  
e10-619-40130,13  
e10-619-40240,0  
e10-619-40314,2  
e10-b18-40912,5  
e30-719-40446,7  
e10-b18-40928,0  
e10-619-40130,0  
e30-719-40436,6  
e10-619-40124,16  
e30-719-40461,14  
e10-619-40310,0  
e10-619-40243,9  
e10-619-40310,10  
e10-619-40139,9  
e10-619-40301,0  
e10-619-40207,0  
e10-b18-40928,0  
e10-619-40175,0  
e10-619-40207,9  
e10-619-40268,0  
e10-619-40268,0  
e10-619-40206,6  
e30-719-40371,0  
e10-619-40299,11  
e10-619-40140,0  
e30-719-40308,5  
e30-719-40526,6  
e10-619-40240,0  
e10-619-40240,10  
e10-619-40198,4  
e30-719-40513,0  
e10-619-40203,0  
e10-619-40130,0  
e10-619-40301,0  
e30-719-40319,11  
e10-619-40270,7  
e10-619-40175,0  
e30-719-40475,10  
e10-619-40310,0  
e10-319-40297,6  
e10-b18-40931,7  
e10-619-40299,0  
e10-619-40175,0  
e10-619-40301,9  
e10-619-40169,0  
e10-619-40147,3  
e10-619-40278,0  
e30-719-40379,4  
e10-619-40280,10  
e10-619-40299,0  
e10-b18-40928,0  
e10-619-40279,10  
e10-619-40174,0  
e10-619-40140,10  
e10-619-40279,0  
e30-719-40403,1  
e10-619-40170,7  
e10-619-40278,12  
e10-619-40289,0  
e10-619-40185,0  
e10-619-40187,0  
e30-719-40450,3  
e30-719-40267,10  
e10-319-40325,0  
e10-619-40279,0  
e10-619-40247,9  
e30-719-40290,8  
e10-619-40139,0  
e10-619-40247,0  
e10-619-40269,0  
e10-b18-40904,11  
e10-319-40333,7  
e10-619-40223,6  
e10-619-40255,9  
e30-719-40275,7  
e10-b18-40941,2  
e30-719-40351,1  
e10-b18-40912,0  
e10-619-40279,0  
e10-b18-40912,0  
e30-719-40586,9  
e10-619-40174,0  
e10-619-40136,5  
e10-619-40187,5  
e10-619-40289,0  
e10-619-40169,8  
e10-619-40215,0  
e10-b18-40928,0  
e10-619-40301,0  
e10-619-40185,0  
e10-619-40306,2  
e10-b18-40928,0  
e10-b18-40928,0  
e10-619-40207,0  
e30-719-40501,12  
e10-619-40203,0  
e10-619-40185,0  
e10-619-40237,10  
e30-719-40285,6  
e10-619-40279,0  
e30-719-40405,7  
e10-619-40240,0  
e30-719-40442,4  
e10-619-40240,0  
e10-619-40206,0  
e10-619-40271,4  
e10-619-40175,17  
e10-619-40226,0  
e30-719-40505,8  
e10-619-40275,9  
e30-719-40406,5  
e10-319-40325,0  
e10-b18-40928,12  
e10-619-40185,0  
e30-719-40513,10  
e30-719-40491,5  
e30-719-40285,0  
e10-619-40279,0  
e10-619-40310,0  
e10-619-40206,0  
e10-619-40185,0  
e10-619-40187,0  
e10-619-40141,8  
e10-b18-40902,0  
e10-b18-40912,0  
e30-719-40513,0  
e10-b18-40928,0  
e10-619-40279,0  
e10-319-40297,0  
e10-619-40174,0  
e30-719-40471,10  
e30-719-40362,8  
e10-619-40206,0  
e10-619-40299,0  
e10-619-40278,0  
e10-619-40174,0  
e10-619-40174,0  
e30-719-40516,14  
e10-b18-40887,4  
e30-719-40359,8  
e10-619-40279,0  
e10-319-40297,0  
e10-319-40297,0  
e10-619-40206,0  
e30-719-40265,4  
e10-619-40175,0  
e30-719-40521,10  
e10-619-40289,6  
e10-619-40130,0  
e10-b18-40896,7  
e10-619-40299,0  
e30-719-40375,7  
e10-b18-40935,7  
e10-b18-40928,0  
e10-619-40215,0  
e10-619-40279,0  
e30-719-40554,0  
e10-619-40210,0  
e10-619-40223,0  
e30-719-40285,0  
e10-619-40140,0  
e30-719-40340,12

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [November 6, 2020, 8:29am UTC](https://discuss.elastic.co/t/scripted-field-unique-count-of-sum-value/254215/9 "2020-11-06T08:29:21Z")

</div>

Okay, I added 'size' parameter in terms aggregation as the default size is 10(or 20?) ,which will control the number of results.

```
{
  "size": 0,
  "aggs": {
    "ids": {
      "terms": {
        "field": "id",
        "size":1000
          
      },
      "aggs": {
        "the_sum": {
          "sum": {
            "field": "value"
          }
        },
        "the_script_bucket": {
          "bucket_selector": {
            "buckets_path": {
              "the_sum": "the_sum.value"
            },
            "script": "params.the_sum == 0"
          }
        }
      }
    },
    "the_count_of_bucket": {
      "stats_bucket": {
        "buckets_path": "ids>the_sum"
      }
    }
  }
}

```

the results is 5.

> {  
> "took": 4,  
> "timed\_out": false,  
> "\_shards": {  
> "total": 5,  
> "successful": 5,  
> "skipped": 0,  
> "failed": 0  
> },  
> "hits": {  
> "total": 253,  
> "max\_score": 0,  
> "hits":   
> },  
> "aggregations": {  
> "ids": {  
> "doc\_count\_error\_upper\_bound": 0,  
> "sum\_other\_doc\_count": 0,  
> "buckets": [  
> {  
> "key": "e10-619-40210",  
> "doc\_count": 1,  
> "the\_sum": {  
> "value": 0  
> }  
> },  
> {  
> "key": "e10-619-40229",  
> "doc\_count": 1,  
> "the\_sum": {  
> "value": 0  
> }  
> },  
> {  
> "key": "e30-719-40270",  
> "doc\_count": 1,  
> "the\_sum": {  
> "value": 0  
> }  
> },  
> {  
> "key": "e30-719-40371",  
> "doc\_count": 1,  
> "the\_sum": {  
> "value": 0  
> }  
> },  
> {  
> "key": "e30-719-40554",  
> "doc\_count": 1,  
> "the\_sum": {  
> "value": 0  
> }  
> }  
> ]  
> },  
> "the\_count\_of\_bucket": {  
> "count": 5,  
> "min": 0,  
> "max": 0,  
> "avg": 0,  
> "sum": 0  
> }  
> }  
> }

---

<div class="post-metadata">

**Author:** ![111207](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/111207/32/53801_2.png) [@111207](https://discuss.elastic.co/u/111207)\
**Post date:** [November 9, 2020, 3:19am UTC](https://discuss.elastic.co/t/scripted-field-unique-count-of-sum-value/254215/10 "2020-11-09T03:19:48Z")

</div>

Hi wangqinghuan

Thank you so mush. The value is correct

But I have the next problem

Please guide for me.How to create the visualization type metric to show value from your script? I'm new for elasticsearch

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 7, 2020, 3:20am UTC](https://discuss.elastic.co/t/scripted-field-unique-count-of-sum-value/254215/11 "2020-12-07T03:20:00Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.