 # SQL Dividing 2 values from 2 queries in CANVAS

Hi, I want to make a division form 2 queries

(SELECT COUNT(id) FROM "production" WHERE state='created')

/

(SELECT COUNT(id) AS number FROM "production")

How would you divide the counts between these two?

Hey @AlfredoC, so you can't do this directly in the SQL but you should be able to accomplish it using the `math` and `string` expression functions. Here is a link to the Canvas expression documentation: https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html

Here is another Discuss post that answers a similar question: Kibana Canvas , Calculate percentage using to aggregations

The important bit is:

``````| essql
query="SELECT order_date, customer_gender FROM \"kibana_sample_data_ecommerce\""
count=10000
| math {
string "count(customer_gender)/"
{filters group="order_date" ungrouped=true
| essql query="SELECT order_date, customer_gender FROM \"kibana_sample_data_ecommerce\""
count=10000
| math "count(customer_gender)"
}
}
``````
1 Like

Hi there @tims...

I am trying to solve a similar problem and the solution you posted was helpful except that my ESSQL queries have a "group by" clause which I want to use to calculate the ratio for each group of items. Any tips on how that could be accomplished? Currently when I try I get the error in Canvas saying "[math] > [string] > [math] > Expressions must return a single number. Try wrapping your expression in mean() or sum()"

Hey @tshayan, I don't have a full example for you but it sounds like maybe you want the `ply` function. Here are the docs:
https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#ply_fn

`ply` lets you subdivide the datatable and pass an expression, something like:
`| ply by="group_by_field" fn={math "sum(count)" | as "count"}`

Hopefully that's enough to get you started.

I think we are one step closer to what I'm trying to achieve but not quite... The issue is that the expression part of the ply function has to return 'a' datatable, which gets applied to every single group (or ply), however, I need different expressions to apply to different groups.

Let's take a fictitious example... Suppose I have a query where I want to find the male percentage in each city.

`select count(*) as maleCount, city from population where gender='male' group by city`

The ficticious result would be something like this:

``````maleCount, City
100, Toronto
200, New York
300, Washington
400, London
``````

Then I need to divide each of those groups by the corresponding total population in each city. So:

`select count(*) as totalPop, city from population group by city`

which returns:

``````totalPop, City
1000, Toronto
2000, New York
3000, Washington
4000, London
``````

So the resulting table should be the first table divided by the second table to return something like this:

``````malePercentage, City
0.1, Toronto
0.2, New York
0.3, Washington
0.4, London
``````

Hope it makes sense what I am trying to do but I have not found a way yet to do it in Canvas.