# How to perform divide math operation in elasticsearch SQL

**URL:** <https://discuss.elastic.co/t/how-to-perform-divide-math-operation-in-elasticsearch-sql/218932>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [February 12, 2020, 9:30am UTC](https://discuss.elastic.co/t/how-to-perform-divide-math-operation-in-elasticsearch-sql/218932 "2020-02-12T09:30:16Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![kiran\_kumar\_sukumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kiran_kumar_sukumar/32/16319_2.png) [@kiran\_kumar\_sukumar](https://discuss.elastic.co/u/kiran_kumar_sukumar)\
**Post date:** [February 12, 2020, 9:30am UTC](https://discuss.elastic.co/t/how-to-perform-divide-math-operation-in-elasticsearch-sql/218932/1 "2020-02-12T09:30:16Z")

</div>

I have logged a few transactions in Elasticsearch and now trying to log a success % metric in Canvas using the elasticsearch SQL

So the logic is this. Out of all the transactions that have ended i need to get the metric of how much % have passed. I log everything to the same index

The following SQL gives me the count of all the transactions that have ended. I mean the total number of transactions

`SELECT count(*) as TotalTransactions FROM "rpa-preprod*" WHERE transactionState ='Ended'`

The next SQL gives the count of all the transactions that have passed

`SELECT count(*) as PassedTransactions FROM "rpa-preprod*" WHERE transactionStatus='Successful'`

Now i need to find how much percentage of transactions have been successful. Basically divide the second count by first count and multiply by 100.

How can i do this in elasticsearch SQL ?

I tried the following SQL and it did not work

`SELECT ((SELECT count(*) as PassedTransactions FROM "rpa-preprod*" WHERE transactionStatus='Successful') * 100 /(SELECT count(*) as TotalTransactions FROM "rpa-preprod*" WHERE transactionState ='Ended')) AS SuccessPercentage FROM DUAL`

---

<div class="post-metadata">

**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\
**Post date:** [February 12, 2020, 10:47am UTC](https://discuss.elastic.co/t/how-to-perform-divide-math-operation-in-elasticsearch-sql/218932/2 "2020-02-12T10:47:58Z")

</div>

Hi @kiran_kumar_sukumar

You can find a similar request [here](https://discuss.elastic.co/t/calculating-purcentage-in-a-metric/211652/2) and in essence, the trick is to split your SQL into two `essql` queries and divide them. Using Canvas from Kibana 7.6 and the sample Flights dataset to get the percentage of canceled trips:

```
filters
| essql 
  query="SELECT count(1) as cnt FROM \"kibana_sample_data_flights\" WHERE Cancelled = true"
| math {string "cnt/" {filters | essql query="SELECT count(1) as cnt FROM \"kibana_sample_data_flights\"" | math "cnt" }}
| formatnumber "0%"
| render

```

Cheers

---

<div class="post-metadata">

**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\
**Post date:** [February 12, 2020, 11:02am UTC](https://discuss.elastic.co/t/how-to-perform-divide-math-operation-in-elasticsearch-sql/218932/3 "2020-02-12T11:02:51Z")

</div>

Sorry, forgot to mention that you have more details about how to present percentages in other Kibana contexts on [this blog post](https://www.elastic.co/blog/how-to-display-data-as-a-percentage-in-kibana-visualizations).

---

<div class="post-metadata">

**Author:** ![kiran\_kumar\_sukumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kiran_kumar_sukumar/32/16319_2.png) [@kiran\_kumar\_sukumar](https://discuss.elastic.co/u/kiran_kumar_sukumar)\
**Post date:** [February 12, 2020, 12:08pm UTC](https://discuss.elastic.co/t/how-to-perform-divide-math-operation-in-elasticsearch-sql/218932/4 "2020-02-12T12:08:34Z")

</div>

Thanks @jsanz. Its working and the numbers are so great that you wouldnt believe it 🙂

---

<div class="post-metadata">

**Author:** ![kiran\_kumar\_sukumar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kiran_kumar_sukumar/32/16319_2.png) [@kiran\_kumar\_sukumar](https://discuss.elastic.co/u/kiran_kumar_sukumar)\
**Post date:** [February 12, 2020, 3:39pm UTC](https://discuss.elastic.co/t/how-to-perform-divide-math-operation-in-elasticsearch-sql/218932/5 "2020-02-12T15:39:37Z")

</div>

Hi @jsanz, Is it possible to specify a default value if the count turns out to be 0

---

<div class="post-metadata">

**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\
**Post date:** [February 12, 2020, 4:01pm UTC](https://discuss.elastic.co/t/how-to-perform-divide-math-operation-in-elasticsearch-sql/218932/6 "2020-02-12T16:01:46Z")

</div>

I'm not a Canvas expert sorry 😓 , but maybe you can try to wrap the expression with a conditional statement to avoid that zero division.

If you can't make it work I'd suggest to create a new post nailing down to that issue in particular.

---

<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:** [March 11, 2020, 4:02pm UTC](https://discuss.elastic.co/t/how-to-perform-divide-math-operation-in-elasticsearch-sql/218932/7 "2020-03-11T16:02:00Z")

</div>

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