Hi
I have a table as below, each time run the query it may return different result
run 1
day1 10
day2 20
day3 25
run second time
day1 10
day2 20
day3 30
day4 10
I want to calculate the average base on how many line there are. So for each table will be calculate as below
day1 10
day2 20
day3 25
avgT 18.333
calculation: (10+20+25)/3=18.333
day1 10
day2 20
day3 30
day4 10
avgT 17.5
Calculation: (10+20+30+10)/4=17.5
Any suggestion how I can achieve that?
Thanks in advance
@samlinsongguo please try the following run anywhere search example. Commands from | makeresults
till | table Days Count
generates the table as per the question:
| makeresults
| eval data="day1 10;day2 20;day3 30;day4 10"
| makemv data delim=";"
| mvexpand data
| makemv data delim=" "
| eval Days=mvindex(data,0),Count=mvindex(data,1)
| fields - _time data
| table Days Count
| streamstats count as sno
| addcoltotals Count labelfield=Days label=AvgT
| filldown sno
| eval Count=if(Days=="AvgT",round(Count/sno,2),Count)
| fields - sno
If the fields in the table above are Days
and Count
, you need to add the following code in your existing search:
<yourCurrentSearch>
| streamstats count as sno
| addcoltotals Count labelfield=Days label=AvgT
| filldown sno
| eval Count=if(Days=="AvgT",round(Count/sno,2),Count)
| fields - sno
@samlinsongguo please try the following run anywhere search example. Commands from | makeresults
till | table Days Count
generates the table as per the question:
| makeresults
| eval data="day1 10;day2 20;day3 30;day4 10"
| makemv data delim=";"
| mvexpand data
| makemv data delim=" "
| eval Days=mvindex(data,0),Count=mvindex(data,1)
| fields - _time data
| table Days Count
| streamstats count as sno
| addcoltotals Count labelfield=Days label=AvgT
| filldown sno
| eval Count=if(Days=="AvgT",round(Count/sno,2),Count)
| fields - sno
If the fields in the table above are Days
and Count
, you need to add the following code in your existing search:
<yourCurrentSearch>
| streamstats count as sno
| addcoltotals Count labelfield=Days label=AvgT
| filldown sno
| eval Count=if(Days=="AvgT",round(Count/sno,2),Count)
| fields - sno
This is so smart Thank you so much