SQL Dividing 2 values from 2 queries in CANVAS

[AlfredoC (Alfredo Cantú)]

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?

[tims (Tim Schnell)]

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)"
      } 
  }

[tshayan (Shayan Talaiyan)]

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()"

[tims (Tim Schnell)]

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.

[tshayan (Shayan Talaiyan)]

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.

Thank in advance.