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"
| sort -"Estimated Gain_Loss"
`````` Tags (3)
1 Solution
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

``````... | 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.

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.

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

So... an average?

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