topic Re: How to calculate average based on 2 fields and group the result by values in the third field? in Splunk Search

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

dominhthe110 — Sat, 25 Jul 2020 16:14:40 GMT

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.

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

isoutamo — Sat, 25 Jul 2020 16:24:49 GMT

| 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

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

dominhthe110 — Sat, 25 Jul 2020 17:18:06 GMT

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.

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

spammenot66 — Sat, 25 Jul 2020 17:54:42 GMT

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.

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

isoutamo — Sat, 25 Jul 2020 18:17:59 GMT

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