# SQL Dividing 2 values from 2 queries in CANVAS **URL:** \ **Category:** Kibana\ **Tags:** canvas\ **Created:** [March 5, 2020, 10:58pm UTC](https://discuss.elastic.co/t/sql-dividing-2-values-from-2-queries-in-canvas/222381 "2020-03-05T22:58:16Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![AlfredoC](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alfredoc/32/56056_2.png) [@AlfredoC](https://discuss.elastic.co/u/AlfredoC)\ **Post date:** [March 5, 2020, 10:58pm UTC](https://discuss.elastic.co/t/sql-dividing-2-values-from-2-queries-in-canvas/222381/1 "2020-03-05T22:58:16Z")
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") ![Captura](https://us1.discourse-cdn.com/elastic/original/3X/5/d/5dad2556e67816434e9a3563429c454cf924be7e.png) How would you divide the counts between these two? ---
**Author:** ![tims](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tims/32/46750_2.png) [@tims](https://discuss.elastic.co/u/tims)\ **Post date:** [March 12, 2020, 1:43pm UTC](https://discuss.elastic.co/t/sql-dividing-2-values-from-2-queries-in-canvas/222381/2 "2020-03-12T13:43:33Z")
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](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](https://discuss.elastic.co/t/kibana-canvas-calculate-percentage-using-to-aggregations/190702/4) The important bit is: ```auto | 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)" } } ``` ---
**Author:** ![tshayan](https://avatars.discourse-cdn.com/v4/letter/t/ecb155/32.png) [@tshayan](https://discuss.elastic.co/u/tshayan)\ **Post date:** [March 31, 2020, 7:22pm UTC](https://discuss.elastic.co/t/sql-dividing-2-values-from-2-queries-in-canvas/222381/3 "2020-03-31T19:22:35Z")
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()" ---
**Author:** ![tims](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tims/32/46750_2.png) [@tims](https://discuss.elastic.co/u/tims)\ **Post date:** [March 31, 2020, 8:59pm UTC](https://discuss.elastic.co/t/sql-dividing-2-values-from-2-queries-in-canvas/222381/4 "2020-03-31T20:59:52Z")
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](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. ---
**Author:** ![tshayan](https://avatars.discourse-cdn.com/v4/letter/t/ecb155/32.png) [@tshayan](https://discuss.elastic.co/u/tshayan)\ **Post date:** [April 2, 2020, 6:53pm UTC](https://discuss.elastic.co/t/sql-dividing-2-values-from-2-queries-in-canvas/222381/5 "2020-04-02T18:53:36Z")
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. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 30, 2020, 6:53pm UTC](https://discuss.elastic.co/t/sql-dividing-2-values-from-2-queries-in-canvas/222381/6 "2020-04-30T18:53:38Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.