# How to Count 3 columns together while doing group by in Elastic Transform in Kibana

**URL:** <https://discuss.elastic.co/t/how-to-count-3-columns-together-while-doing-group-by-in-elastic-transform-in-kibana/216405>\
**Category:** Elasticsearch\
**Created:** [January 24, 2020, 11:06am UTC](https://discuss.elastic.co/t/how-to-count-3-columns-together-while-doing-group-by-in-elastic-transform-in-kibana/216405 "2020-01-24T11:06:35Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vijay\_Gupta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vijay_gupta/32/53067_2.png) [@Vijay\_Gupta](https://discuss.elastic.co/u/Vijay_Gupta)\
**Post date:** [January 24, 2020, 11:06am UTC](https://discuss.elastic.co/t/how-to-count-3-columns-together-while-doing-group-by-in-elastic-transform-in-kibana/216405/1 "2020-01-24T11:06:35Z")

</div>

I have below data set in my index and I want to summarize my index via transform feature in kibana (yellow column shows count of available number for reference)

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/c/d/cdf43838db251fe87fc5224c7f8b599930917147.png)

I am able to get individual columns count while doing group by, with below code in Transform.

```
{
  "group_by": {
    "customer_id": {
      "histogram": {
        "field": "customer_id",
        "interval": "10"
      }
    },  
  },
  "aggregations": {
    "phone1": {
      "value_count": {
        "field": "phone1"
      }
    },
    "phone2": {
      "value_count": {
        "field": "phone2"
      }
    },
    "phone3": {
      "value_count": {
        "field": "phone3"
      }
    }
  }
}

```

how can I count values of 3 different columns (phone1, phone2, phone3) together group by customer\_id, so that count of all the numbers come together not seperately  
A similar sql query would be like below -

```
SELECT count("phone1") + count("phone2") + count("phone3") AS no_of_phone from table_name 
group by "customer_id"

```

Expected output -  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/1/2/1263d687bcaf335dd8af51580c85b17922ee7f2d.png)

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [January 27, 2020, 1:51pm UTC](https://discuss.elastic.co/t/how-to-count-3-columns-together-while-doing-group-by-in-elastic-transform-in-kibana/216405/2 "2020-01-27T13:51:07Z")

</div>

Hi,

there is currently no easy way to do this. However you can use a scripted metric aggregation (bucket script might work, too). There is no exact [example](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-examples.html), but I hope you get inspired and are able to implement your usecase.

---

<div class="post-metadata">

**Author:** ![Vijay\_Gupta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vijay_gupta/32/53067_2.png) [@Vijay\_Gupta](https://discuss.elastic.co/u/Vijay_Gupta)\
**Post date:** [January 27, 2020, 2:08pm UTC](https://discuss.elastic.co/t/how-to-count-3-columns-together-while-doing-group-by-in-elastic-transform-in-kibana/216405/3 "2020-01-27T14:08:00Z")

</div>

Hi @Hendrik_Muhs  
Thanks for the reply, I thought it's a normal use case and it might be documented somewhere in elastic search website. However thank you for suggestions I'll look into scripted metric and bucket script.

---

<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:** [February 24, 2020, 2:08pm UTC](https://discuss.elastic.co/t/how-to-count-3-columns-together-while-doing-group-by-in-elastic-transform-in-kibana/216405/4 "2020-02-24T14:08:06Z")

</div>

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