# Kibana metric, can I get: sum of \`NumberOfPeople\` who attended clinics with a unique\`ClinicName\`?

**URL:** <https://discuss.elastic.co/t/kibana-metric-can-i-get-sum-of-numberofpeople-who-attended-clinics-with-a-unique-clinicname/377789>\
**Category:** Kibana\
**Created:** [May 5, 2025, 4:26am UTC](https://discuss.elastic.co/t/kibana-metric-can-i-get-sum-of-numberofpeople-who-attended-clinics-with-a-unique-clinicname/377789 "2025-05-05T04:26:55Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Robin\_Gorry](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/robin_gorry/32/142231_2.png) [@Robin\_Gorry](https://discuss.elastic.co/u/Robin_Gorry)\
**Post date:** [May 5, 2025, 4:26am UTC](https://discuss.elastic.co/t/kibana-metric-can-i-get-sum-of-numberofpeople-who-attended-clinics-with-a-unique-clinicname/377789/1 "2025-05-05T04:26:55Z")

</div>

v 8.17.4  
In my metric visualisation I would like to show the sum of `NumberOfPeople` who attended clinics with a unique`ClinicName` .  
Is this possible? I can see how I can get `sum(NumberOfPeople)` and `unique_count(ClinicName)`.  
But is it possible to get the the `sum(NumberOfPeople)` who attended `unique_count(ClinicName)` ?

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [May 5, 2025, 6:35am UTC](https://discuss.elastic.co/t/kibana-metric-can-i-get-sum-of-numberofpeople-who-attended-clinics-with-a-unique-clinicname/377789/2 "2025-05-05T06:35:51Z")

</div>

Can you give a sample data set and result?

Like

Stephen clinic A  
Stephen clinic B  
Robin Clinic C  
Dave Clinic A  
Dave Clinic D

What is you desired result?

---

<div class="post-metadata">

**Author:** ![Robin\_Gorry](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/robin_gorry/32/142231_2.png) [@Robin\_Gorry](https://discuss.elastic.co/u/Robin_Gorry)\
**Post date:** [May 5, 2025, 8:11am UTC](https://discuss.elastic.co/t/kibana-metric-can-i-get-sum-of-numberofpeople-who-attended-clinics-with-a-unique-clinicname/377789/3 "2025-05-05T08:11:40Z")

</div>

Thanks for your reply Stephen.  
ClinicName's will always have the same NumberOfPeople, so if I can select all unique ClinicName's and then sum the NumberOfPeople of each ClinicName, I will have a total of: **9**

| ClinicName | NumberOfPeople |
| --- | --- |
| Robin | 2 |
| Robin | 2 |
| Robin | 2 |
| Dave | 3 |
| Stephen | 4 |
| Stephen | 4 |

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [May 5, 2025, 4:59pm UTC](https://discuss.elastic.co/t/kibana-metric-can-i-get-sum-of-numberofpeople-who-attended-clinics-with-a-unique-clinicname/377789/4 "2025-05-05T16:59:00Z")

</div>

Is there a timestamp with the data then could probably use ther `latest` function on each ClinicName

---

<div class="post-metadata">

**Author:** ![Robin\_Gorry](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/robin_gorry/32/142231_2.png) [@Robin\_Gorry](https://discuss.elastic.co/u/Robin_Gorry)\
**Post date:** [May 9, 2025, 3:28am UTC](https://discuss.elastic.co/t/kibana-metric-can-i-get-sum-of-numberofpeople-who-attended-clinics-with-a-unique-clinicname/377789/5 "2025-05-09T03:28:39Z")

</div>

Thanks for your reply @stephenb.  
I can't find the `latest` function in the es|ql docs, or anywhere else.  
Could you please elaborate on how I can do this, or at least point me in the right direction?

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [May 9, 2025, 4:03am UTC](https://discuss.elastic.co/t/kibana-metric-can-i-get-sum-of-numberofpeople-who-attended-clinics-with-a-unique-clinicname/377789/6 "2025-05-09T04:03:36Z")

</div>

Apologies in Lens it is Last Value

Do you have a timestamp on the data?

And how many clinics are there? (There is a limit on aggs

We can build a metric if you have a timestamp, some way to get the last value

---

<div class="post-metadata">

**Author:** ![Robin\_Gorry](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/robin_gorry/32/142231_2.png) [@Robin\_Gorry](https://discuss.elastic.co/u/Robin_Gorry)\
**Post date:** [May 13, 2025, 8:36pm UTC](https://discuss.elastic.co/t/kibana-metric-can-i-get-sum-of-numberofpeople-who-attended-clinics-with-a-unique-clinicname/377789/7 "2025-05-13T20:36:31Z")

</div>

@stephenb thanks for your reply.  
Yes we have a date-time field on the index called `CreatedDate` and at the moment there's only 6000 records.

Just to restate my goal, Sum `NumberOfPeople` where `ClinicName` is distinct.  
Total would be: **9**

| ClinicName | NumberOfPeople | CreatedDate |
| --- | --- | --- |
| Robin | 2 | 2023-08-26 10:51:54 |
| Robin | 2 | 2023-08-25 10:41:54 |
| Robin | 2 | 2023-08-24 10:31:54 |
| Dave | 3 | 2023-08-23 10:21:54 |
| Stephen | 4 | 2023-02-26 12:51:54 |
| Stephen | 4 | 2023-01-26 10:51:54 |

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [May 14, 2025, 4:47am UTC](https://discuss.elastic.co/t/kibana-metric-can-i-get-sum-of-numberofpeople-who-attended-clinics-with-a-unique-clinicname/377789/8 "2025-05-14T04:47:11Z")

</div>

Hi @Robin_Gorry

Here is the data I used

```auto
PUT /clinics
{
  "mappings": {
    "properties": {
      "ClinicName": {
        "type": "keyword"
      },
      "NumberOfPeople": {
        "type": "integer"
      },
      "CreatedDate": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss"
      }
    }
  }
}

POST /clinics/_bulk
{ "index": {} }
{ "ClinicName": "Robin", "NumberOfPeople": 2, "CreatedDate": "2023-08-26 10:51:54" }
{ "index": {} }
{ "ClinicName": "Robin", "NumberOfPeople": 2, "CreatedDate": "2023-08-25 10:41:54" }
{ "index": {} }
{ "ClinicName": "Robin", "NumberOfPeople": 2, "CreatedDate": "2023-08-24 10:31:54" }
{ "index": {} }
{ "ClinicName": "Dave", "NumberOfPeople": 3, "CreatedDate": "2023-08-23 10:21:54" }
{ "index": {} }
{ "ClinicName": "Stephen", "NumberOfPeople": 4, "CreatedDate": "2023-02-26 12:51:54" }
{ "index": {} }
{ "ClinicName": "Stephen", "NumberOfPeople": 4, "CreatedDate": "2023-01-26 10:51:54" }

POST /_query/async?drop_null_columns&format=txt
{
  "query": "FROM clinics | STATS max_num_people = MAX(NumberOfPeople) BY ClinicName | STATS total_people = SUM(max_num_people)"
}

```

```auto
# Result
#! No limit defined, adding default limit of [1000]
 total_people  
---------------
9              

```

Or run the first 2 commands and then go to discover ...

```auto
FROM clinics
  | STATS max_num_people = MAX(NumberOfPeople) BY ClinicName
  | STATS total_people = SUM(max_num_people)

```

Technically that just takes the sum of the max which may work.

 ![Screenshot 2025-05-13 at 9.46.01 PM](https://us1.discourse-cdn.com/elastic/original/3X/4/b/4b10252ec3232c673498e3239ac2bda10dc91d74.png)

In Lens you will need to create a data View with

 ![Screenshot 2025-05-14 at 7.10.10 AM](https://us1.discourse-cdn.com/elastic/original/3X/7/4/744c4dee25ed20df7e5ff112053f9561a0938bcd.jpeg)

Then Create a Lens Metrics

We will get the last value of NumberOfPeople for each Clinic Name and then Sum Them

Primary Metrics  
Last Value : NumberOfPeople

 ![Screenshot 2025-05-14 at 7.13.05 AM](https://us1.discourse-cdn.com/elastic/original/3X/4/b/4b32dc94febd8eb1283e9681898357e79a4b3e4b.png)

Then Breakdown by Top Value ClinicName  
1000  
Then Collapse by Sum

 ![Screenshot 2025-05-14 at 7.13.38 AM](https://us1.discourse-cdn.com/elastic/original/3X/c/4/c4e7b6c0543529efdabdd435b4ee96f3100d64e8.png)

Hope This Helps

---

<div class="post-metadata">

**Author:** ![iTiago](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/itiago/32/142800_2.png) [@iTiago](https://discuss.elastic.co/u/iTiago)\
**Post date:** [May 14, 2025, 3:46pm UTC](https://discuss.elastic.co/t/kibana-metric-can-i-get-sum-of-numberofpeople-who-attended-clinics-with-a-unique-clinicname/377789/9 "2025-05-14T15:46:52Z")

</div>

Look this [Cumulative cardinality aggregation | Elastic Documentation](https://www.elastic.co/docs/reference/aggregations/search-aggregations-pipeline-cumulative-cardinality-aggregation)

---

<div class="post-metadata">

**Author:** ![Robin\_Gorry](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/robin_gorry/32/142231_2.png) [@Robin\_Gorry](https://discuss.elastic.co/u/Robin_Gorry)\
**Post date:** [May 15, 2025, 7:52pm UTC](https://discuss.elastic.co/t/kibana-metric-can-i-get-sum-of-numberofpeople-who-attended-clinics-with-a-unique-clinicname/377789/10 "2025-05-15T19:52:27Z")

</div>

Thank you again @stephenb for this great response.  
I was very close to getting it right but I needed the MAX(NumberOfPeople).  
I've learnt quite a few things from your full response, much appreciated.