How to calculate average based on 2 fields and group the result by values in the third field?

[dominhthe110]

Hi everyone, 

This is the first time, I've used Splunk.  I have the data like this:

ORDER_ID	PRICE	GROUP
00001	10	A
00002	20	B
00003	20	A
00004	15	B
00005	23.3	C

And I want to calculate the average price for each group, which returns the result like this:

GROUP	AVERAGE_PRICE
A	15
B	17.5
C	23.3

In order to do this average calculation, I know that I have to calculate the total number of ORDER_ID and the sum of PRICE for each GROUP. But I don't know how to perform this calculation in SPL. Please kindly guild me through this. 

Regard.

[isoutamo · Accepted solution]

Hi

| makeresults
| eval _raw="ORDER_ID, PRICE, GROUP
00001,10,A
00002,20,B
00003,20,A
00004,15,B
00005,23.3,C"
| multikv forceheader=1
| stats avg(PRICE) AS AVERAGE_PRICE by GROUP

[dominhthe110]

Thank you a lot. It worked.
But I just wonder is there any other ways that I can calculate it step by step not using the avg() function? 
Assump that the data is indexed so that we don't need the "eval _raw="..." line.

[spammenot66]

the same solution would work using indexed data. you just have to apply the same concept. He's proved out his point by creating sandbox data basedon your initial question. BTW, i would say his response is the best solution. I wouldn't recommend over complicating it. 

[isoutamo]

Yes it works without first lines (like @spammenot66 @said), which only create data to do actual calculation (last line). Just use it with your indexed data. 
r. Ismo