# SUM for colum1 and filter

**URL:** <https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [March 23, 2021, 3:05pm UTC](https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110 "2021-03-23T15:05:39Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![EduardoM](https://avatars.discourse-cdn.com/v4/letter/e/bbe5ce/32.png) [@EduardoM](https://discuss.elastic.co/u/EduardoM)\
**Post date:** [March 23, 2021, 3:05pm UTC](https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110/1 "2021-03-23T15:05:39Z")

</div>

I am trying to do the following:

Select Sum (durationprev) from ptx\*-… where location = abc0011 and status = auto as running

AVAL= running / total.

```
filters 
| essql 
  query="SELECT SUM(duration) AS running
FROM \"data-*\"
WHERE QUERY ('ptx')
AND event = 'ab'

```

> AND status ='AUTO'"

```
| math "running"
    | metric "Countries" 
      metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48} 
      labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0.[000]"
    | render

```

in general: that is the sum of column A (time) and in another column B has AUTO and MANUEL status.  
And I just want the sum total of the status AUTO and I don't want to filter with "

> AND

" can it be done? and I hope that I have explained myself.  
could someone please help me?.

---

<div class="post-metadata">

**Author:** ![Felix\_Roessel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/felix_roessel/32/41623_2.png) [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)\
**Post date:** [March 23, 2021, 4:09pm UTC](https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110/2 "2021-03-23T16:09:07Z")

</div>

It looks like it should work. Whats the error message / issue?  
Do you need more examples of Canvas boards?

---

<div class="post-metadata">

**Author:** ![EduardoM](https://avatars.discourse-cdn.com/v4/letter/e/bbe5ce/32.png) [@EduardoM](https://discuss.elastic.co/u/EduardoM)\
**Post date:** [March 24, 2021, 8:13am UTC](https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110/3 "2021-03-24T08:13:02Z")

</div>

yes, I do, I need to see more examples of Canvas board, Thanks a lot. there is not a error or issue, but I want to have the SUM total (duration) AS running and too the SUM (duration) but only of the STATUS=AUTO and then I could to do the percentage in "metric" (SUM (duration, status=auto) / running. I need to get the percentage of the duration between auto status.

---

<div class="post-metadata">

**Author:** ![Felix\_Roessel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/felix_roessel/32/41623_2.png) [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)\
**Post date:** [March 24, 2021, 11:32am UTC](https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110/4 "2021-03-24T11:32:11Z")

</div>

okay got it.

In this case I would do a `group by status` within the SQL statement. Then you have the `SUM of duration` per status in the response.  
Next step is using [filterrows](https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#filterrows_fn) to get the value of each status. You could store this e.g. in a variable.  
In another variable you save the result of the math expression that is doing the sum of both.

Finally you insert your percentage calculation in your visualization.

I think the best example you can fine here. That's one I've made a while ago to help users with their first steps in Canvas.

> **[APM Services overview canvas dashboard at elastic content share](https://elastic-content-share.eu/downloads/apm-services-overview-canvas/)**
>
> The APM services canvas is an adaptive canvas board that fits to your APM data. If you running Elastic APM -- try it for free!

---

<div class="post-metadata">

**Author:** ![EduardoM](https://avatars.discourse-cdn.com/v4/letter/e/bbe5ce/32.png) [@EduardoM](https://discuss.elastic.co/u/EduardoM)\
**Post date:** [March 25, 2021, 4:43pm UTC](https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110/5 "2021-03-25T16:43:27Z")

</div>

First of all I must clarify that I do not know anything about programming, I am very new to these issues and before seeing your message, I tried to do this but I don't know if it is a good option. to use the variables in math, this would be missing, right?

```
filters
| var_set name="running" value={
    essql query="SELECT SUM(duration) FROM \"data-*\" WHERE QUERY ('ptx') AND event = 'ab' AND statu ='AUTO' "
  }
| var_set name="total" value={
    essql query="SELECT SUM(duration) FROM \"data-*\" WHERE QUERY ('ptx') AND event = 'ab' "
  }
|math ("running / total") (how to do ?) 

```

Thanks advance

---

<div class="post-metadata">

**Author:** ![Felix\_Roessel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/felix_roessel/32/41623_2.png) [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)\
**Post date:** [March 25, 2021, 6:01pm UTC](https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110/6 "2021-03-25T18:01:34Z")

</div>

This should do it. Not sure if it is the shortest way but it will work  
`math {string {var running} "/" {var total}}`

---

<div class="post-metadata">

**Author:** ![EduardoM](https://avatars.discourse-cdn.com/v4/letter/e/bbe5ce/32.png) [@EduardoM](https://discuss.elastic.co/u/EduardoM)\
**Post date:** [March 26, 2021, 1:56pm UTC](https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110/7 "2021-03-26T13:56:56Z")

</div>

unfortunately it didn't work

---

<div class="post-metadata">

**Author:** ![Felix\_Roessel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/felix_roessel/32/41623_2.png) [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)\
**Post date:** [March 29, 2021, 7:30am UTC](https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110/8 "2021-03-29T07:30:48Z")

</div>

> [@EduardoM](#):
>
> `essql query="SELECT SUM(duration) FROM \"data-*\" WHERE QUERY ('ptx') AND event = 'ab' AND statu ='AUTO' "`

Ich think you need to change this part a bit, as you do not extract the field to be stored in the variable.

Try something like this:  
essql query="SELECT SUM(duration) **as duration** FROM "data-\*" WHERE QUERY ('ptx') AND event = 'ab' AND statu ='AUTO' " **| getCell duration**

---

<div class="post-metadata">

**Author:** ![EduardoM](https://avatars.discourse-cdn.com/v4/letter/e/bbe5ce/32.png) [@EduardoM](https://discuss.elastic.co/u/EduardoM)\
**Post date:** [March 29, 2021, 1:54pm UTC](https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110/9 "2021-03-29T13:54:22Z")

</div>

```
    filters
| var_set name="running" value={
    essql query="SELECT SUM(duration) AS duration FROM \"data-*\" WHERE QUERY ('ptx') AND statu ='AUTO' " | getCell duration
  }
| var_set name="total" value={
    essql query="SELECT SUM(duration) AS total FROM \"data-*\" WHERE QUERY ('ptx') AND event = 'data' " | getCell total
  }
| math {string {var running} "/" {var total}}

```

Thanks for the recommendation but this is not working, it is a shame because it is an important piece of information and it cannot be generated in this program.

---

<div class="post-metadata">

**Author:** ![EduardoM](https://avatars.discourse-cdn.com/v4/letter/e/bbe5ce/32.png) [@EduardoM](https://discuss.elastic.co/u/EduardoM)\
**Post date:** [March 30, 2021, 1:07pm UTC](https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110/10 "2021-03-30T13:07:03Z")

</div>

hi i tried this with MARKDOWN and it almost worked. I say Almost, if I just delete the variable "all", the other variable shows it is progress but not satisfactory. Could you help me because it does not take the second variable and the mathematical operation is performed.

```
var_set "running" 
  value={filters | essql query="SELECT SUM(duration) AS part FROM \"data-*\" WHERE QUERY ('ptx') AND status ='AUTO'" | getCell column="part "}
var_set "total" 
  value={filters | essql query="SELECT SUM(duration) AS time FROM \"data-*\" WHERE QUERY ('ptx') " | getCell column="time "}
| filters
| markdown {var "running/ total"}
  font={font align="center" color="#444444" family="'Open Sans', Helvetica, Arial, sans-serif" italic=false size=30 underline=false weight="bold"}
| render
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 27, 2021, 1:07pm UTC](https://discuss.elastic.co/t/sum-for-colum1-and-filter/268110/11 "2021-04-27T13:07:34Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.