How to calculate the average for all values in a single column?

[jpolcari]

This one seems pretty straight forward, but I haven't been able to find an answer anywhere. I'm looking to calculate the average for all the values in a single column, kind of like addcoltotals. Example of what I am trying to achieve:

User        Time(Hours)
user1       1.2
user2       2.0
user3       0.5
            (average here)

[jotne]

I did found an better way to do this.

| makeresults | eval value = "1.2 2.5 0.5" | makemv value | mvexpand value
| eval count=1
| addcoltotals
| eval value=if(count>1,value/count,value)
| fields - count

Result

_time	        value
2022-06-04 10:08:55	1.2
2022-06-04 10:08:55	2.5
2022-06-04 10:08:55	0.5
 	                1.4

[ddrillic]

You would think that there would be a "family" of commands similar to addcoltotals, such as addcolaverage...

[jpolcari]

That is what I was hoping for. Maybe one day!

[lguinn2 · Accepted solution]

You can do it like this

yoursearchhere
| stats sum(Time) as totalTime by User
| appendpipe [ stats avg(totalTime) as totalTime | eval User = "Average Time" ]
| rename totalTime as "Time (Hours)"

The appendpipe commands examines the results in the pipeline, and in this case, calculates an average. The results of the appendpipe command are added to the end of the existing results. Notice that I used the same field names within the appendpipe command, so that the new results would align in the same columns.

[jpolcari]

Thank you! That is exactly what I needed.

[jperezes]

I am trying to do something similar, but this solution is not working to me.
avg(totalTime) returns totalTime as it is the average of a single value. So I end up with a table for total times by user instead of the average by user.
I had to add the total number of occurrences and at the end divide the total value for the number of occurrences per user.

rgds,
Juan