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`