Splunk Search
Highlighted

How do you add all the values in a column to get the total and then divide the total by the number of rows in the column?

New Member

The column to the right has a total of the percentage increase, but I would like to take that total and divide it by the number of rows that made the total.

source="impositions*.csv" sourcetype="stock-Positions:csv"
| dedup Symbol
| rename "Market Value" as mv
| rename "Estimated Gain
Loss" as egl
| rex field=mv mode=sed "s/,//"
| rex field=egl mode=sed "s/,//"
| rex field=Price mode=sed "s/,//"
| replace $* with * in Price
| replace $* with * in egl
| replace N/A with 0 in egl
| replace $* with * in mv
| replace .000 with * in Quantity
| eval originalprice = (mv - egl)/Quantity
| eval eglneg=egl
| replace -
with * in eglneg
| replace $* with * in eglneg
| replace N/A with 0 in eglneg
| eval originalpriceneg = (mv)+(eglneg)

| eval originalprice=if(isnull(originalprice), originalpriceneg, originalprice)

| eval Percent = round(((Price - originalprice)/originalprice*100),0)

| table Symbol Description Quantity originalprice Price egl  mv  Percent
| rename originalprice as "Purchased Price"
| rename Price as "Current Price"
| rename mv as "Market Value"
| rename Percent as "Percentage Increase"
| rename  egl as "Estimated Gain_Loss"
| rename Quantity as "# of Shares"
| addcoltotals 
| sort -"Estimated Gain_Loss"

alt text

0 Karma
Highlighted

Re: How do you add all the values in a column to get the total and then divide the total by the number of rows in the column?

SplunkTrust
SplunkTrust

If you want to add additional columns with statistics, use eventstats:

... | eventstats count sum(foo) avg(foo)

If you just want the statistics, use stats:

... | stats count sum(foo) avg(foo)

I'm not sure what particular statistic you're asking for, so those two are just examples similar to the words you used.

View solution in original post

0 Karma
Highlighted

Re: How do you add all the values in a column to get the total and then divide the total by the number of rows in the column?

New Member

Thank you, but that is not exactly what I am looking for.

Field=percentage with a value=.25
and the second value in the field
Field=percentage with a value=.50

If I add them I get a total of .75, but I want to divide that total by the number of values added to make it. In this case it would be .75/2=.375

So if I I have a column of percentages, I want to add them all up and then divide the total by the number of values that made the sum.

0 Karma
Highlighted

Re: How do you add all the values in a column to get the total and then divide the total by the number of rows in the column?

SplunkTrust
SplunkTrust

So... an average?

0 Karma
Highlighted

Re: How do you add all the values in a column to get the total and then divide the total by the number of rows in the column?

New Member

My bad. You were absolutely correct.
Thank you

0 Karma