Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- How do you add all the values in a column to get t...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

ihaveasplunkacc

New Member

02-15-2020
02:43 PM

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="im*positions*.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

| eval originalprice = (mv - egl)/Quantity

| eval eglneg=egl

| replace -

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

1 Solution

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

martin_mueller

SplunkTrust

02-15-2020
03:34 PM

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.

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

ihaveasplunkacc

New Member

02-15-2020
03:45 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

martin_mueller

SplunkTrust

02-15-2020
03:59 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

ihaveasplunkacc

New Member

02-15-2020
04:12 PM

My bad. You were absolutely correct.

Thank you