Sum 2 queries in Canvas element

[AClerk]

Hello,
I need to sum up 2 numbers from 2 different queries.
I read this post but was not able to achieve any result.
Can someone please focus me on the solution?
Need to achieve something like this:

select amount from my-index where bla=x 
+ 
select amount from my-index where bla=y

Cheers!

[Marco_Liberati]

Hi @AClerk ,

I've used the same expression in the post you referenced to and it works by changing the / divide operator with the + one. Then you have to change the queries with yours, and all the referenced fields:

filters group="order_date" group="gender" ungrouped=true
| essql 
    query="SELECT order_date, customer_gender FROM \"kibana_sample_data_ecommerce\""
    count=10000
| math { 
    string "count(customer_gender)+" // <- changed the operation from / to + here
      {filters group="order_date" ungrouped=true 
       | essql query="SELECT order_date, customer_gender FROM \"kibana_sample_data_ecommerce\""
           count=10000
       | math "count(customer_gender)"
      } 
  }

The idea is to build a string with the math formula, and then run the math operation over it

[AClerk]

Will give it another try.
Thanks for your reply.

[Felix_Roessel]

Personally I would prefer storing the results of the queries in variables first and do the calculation in a second step.
This way the expression is easier to understand I think.

[AClerk]

@Felix_Roessel
Do you have an example?
Was looking for this option.
Thanks

[Felix_Roessel]

It could look somehow like this...

filters
  | essql query={string "SELECT service.name FROM \"apm*\" where  processor.event = 'error' "}
  | var_set name="field1" value={getCell "field1"}
| filters
  | essql query={string "SELECT service.name FROM \"apm*\" where  processor.event = 'transaction' "}
  | var_set name="field2" value={getCell "field2"}
| math {string {var field1} " + " {var field2} }