# Calculate percentage based on status of max per group in Elasticsearch
**URL:** https://discuss.elastic.co/t/calculate-percentage-based-on-status-of-max-per-group-in-elasticsearch/343171
**Category:** Elasticsearch
**Created:** [September 16, 2023, 6:45am UTC](https://discuss.elastic.co/t/calculate-percentage-based-on-status-of-max-per-group-in-elasticsearch/343171 "2023-09-16T06:45:54Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@carollyl](https://discuss.elastic.co/u/carollyl)
#### Post date: [September 16, 2023, 6:45am UTC](https://discuss.elastic.co/t/calculate-percentage-based-on-status-of-max-per-group-in-elasticsearch/343171/1 "2023-09-16T06:45:54Z")
Given the dataset below, I'd like to calculate percentage of status over unique count of workflow.
```auto
id,workflow,status
1,A,FAILURE
2,A,ABORTED
3,A,SUCCESS
4,A,SUCCESS
1,B,FAILURE
2,B,SUCCESS
3,B,FAILURE
1,C,FAILURE
2,C,FAILURE
1,D,FAILURE
2,D,SUCCESS
3,D,FAILURE
4,D,FAILURE
5,D,FAILURE
```
Always refer to the status of max id.
```auto
A - SUCCESS (refer to max id is 4)
B - FAILURE (refer to max id is 3)
C - FAILURE (refer max id is 2)
D - FAILURE (refer max id is 5)
```
Expected aggregated results as follow:
```auto
unique count of workflow: 4 // workflow A, B, C, and D
SUCCESS: (1/4) * 100 = 25% // workflow A
FAILURE: (3/4) * 100 = 75% // workflow B, C, and D
```
Appreciate it if you can advise the query to get the 25% (SUCCESS) and 75% (FAILURE).
---
### Author:  [@carollyl](https://discuss.elastic.co/u/carollyl)
#### Post date: [September 18, 2023, 5:09am UTC](https://discuss.elastic.co/t/calculate-percentage-based-on-status-of-max-per-group-in-elasticsearch/343171/2 "2023-09-18T05:09:20Z")
Hi @stephenb, appreciate it if you can provide your assistance to my problem statement.
---
### Author:  [@carollyl](https://discuss.elastic.co/u/carollyl)
#### Post date: [September 18, 2023, 3:35pm UTC](https://discuss.elastic.co/t/calculate-percentage-based-on-status-of-max-per-group-in-elasticsearch/343171/3 "2023-09-18T15:35:58Z")
Hi @stephenb Is it possible to come out with a query without additional of runtime field(s)?
---
### Author:  [@stephenb](https://discuss.elastic.co/u/stephenb)
#### Post date: [September 18, 2023, 6:54pm UTC](https://discuss.elastic.co/t/calculate-percentage-based-on-status-of-max-per-group-in-elasticsearch/343171/4 "2023-09-18T18:54:11Z")
Hi @carollyl
I suggest that if you are going to many of these types of queries you spend some time with the DSL and learn the aggregations.
Whether you need a runtime field or not is unclear to me.
I also suggested that you look at transforms as it may make the data easier to work with, that may be worth your time.
There are many questions and few helpers... perhaps I can take a look at this later in the week but I may not be able to.
---
### Author:  [@carollyl](https://discuss.elastic.co/u/carollyl)
#### Post date: [September 19, 2023, 3:56pm UTC](https://discuss.elastic.co/t/calculate-percentage-based-on-status-of-max-per-group-in-elasticsearch/343171/5 "2023-09-19T15:56:11Z")
I managed to create the rate (success, failure, aborted) with runtime\_mappings. Please share with me if there is a more optimized approach.
# Query
```auto
GET discuss/_search
{
"size": 0,
"runtime_mappings": {
"status_success": {
"type": "long",
"script": {
"source": "if (doc['status'].value == 'SUCCESS') {emit(1)} else {emit(0)}"
}
},
"status_failure": {
"type": "long",
"script": {
"source": "if (doc['status'].value == 'FAILURE' || doc['status'].value == 'UNSTABLE') {emit(1)} else {emit(0)}"
}
},
"status_aborted": {
"type": "long",
"script": {
"source": "if (doc['status'].value == 'ABORTED') {emit(1)} else {emit(0)}"
}
}
},
"aggs": {
"by_workflow": {
"terms": {
"field": "workflow"
},
"aggs": {
"top_status": {
"top_metrics": {
"metrics": [
{"field": "status_success"},
{"field": "status_failure"},
{"field": "status_aborted"}
],
"sort": {
"id": "desc"
}
}
}
}
},
"success_rate": {
"avg_bucket": {
"buckets_path": "by_workflow>top_status[status_success]",
"gap_policy": "skip",
"format": "#%"
}
},
"failure_rate": {
"avg_bucket": {
"buckets_path": "by_workflow>top_status[status_failure]",
"gap_policy": "skip",
"format": "#%"
}
}
"aborted_rate": {
"avg_bucket": {
"buckets_path": "by_workflow>top_status[status_aborted]",
"gap_policy": "skip",
"format": "#%"
}
}
}
```
# Results
```auto
{
"took" : 8,
"timed_out" : false,
"_shards" : {
"total" : 2,
"successful" : 2,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 421,
"relation" : "eq"
},
"max_score" : null,
"hits" : []
},
"aggregations" : {
"by_workflow" : {
"doc_count_error_upper_bound" : 2,
"sum_other_doc_count" : 179,
"buckets" : [
{
"key" : "A",
"doc_count" : 102,
"top_status" : {
"top" : [
{
"sort" : [
23
],
"metrics" : {
"status_success" : 0,
"status_failure" : 1,
"status_aborted" : 0
}
}
]
}
},
{
"key" : "B",
"doc_count" : 78,
"top_status" : {
"top" : [
{
"sort" : [
47
],
"metrics" : {
"status_success" : 1,
"status_failure" : 0,
"status_aborted" : 0
}
}
]
}
},
{
"key" : "C",
"doc_count" : 62,
"top_status" : {
"top" : [
{
"sort" : [
57
],
"metrics" : {
"status_success" : 1,
"status_failure" : 0,
"status_aborted" : 0
}
}
]
}
}
]
},
"success_rate" : {
"value" : 0.6666666666666666,
"value_as_string" : "67%"
},
"failure_rate" : {
"value" : 0.3333333333333333,
"value_as_string" : "33%"
},
"aborted_rate" : {
"value" : 0.0,
"value_as_string" : "0%"
}
}
}
```
I'd like to create a pie chart (vega/vega-lite) using the `success_rate`, `failure_rate`, and `aborted_rate`. Appreciate it if you can share any insights how to transform the rate into accessible array of data.
---
### Author:  [@stephenb](https://discuss.elastic.co/u/stephenb)
#### Post date: [September 19, 2023, 4:27pm UTC](https://discuss.elastic.co/t/calculate-percentage-based-on-status-of-max-per-group-in-elasticsearch/343171/6 "2023-09-19T16:27:48Z")
Hi @carollyl Great progress / Nicely Done, unfortunately, I do not work with Vega. You should open a topic with Vega in the title and see if someone can help, it is a very specialized area.
I actually think that you could add those runtime fields to the mapping (as opposed to the query)
Then you could use a transform
Then you could probably just use a normal lens with some formulas to get the correct results.
I would look at that as much as vega... But that would be my approach as I don't know Vega.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [October 17, 2023, 4:27pm UTC](https://discuss.elastic.co/t/calculate-percentage-based-on-status-of-max-per-group-in-elasticsearch/343171/7 "2023-10-17T16:27:50Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.