topic Re: How to calculate total when aggregating using stats max(field)? in Splunk Search

How to calculate total when aggregating using stats max(field)?

LearningGuy — Wed, 11 Oct 2023 17:19:32 GMT

How to calculate total when aggregating using stats max(field)?
Thank you for your help.

Max Total Score is the total score of maximum score for each Score field when aggregating all rows using stats: max(Score1), max(Score2), max(Score3).
TotalScore is the total of each Score field for each row (without aggregation)

This is the output I need

Class	Name	Subject	TotalScore	Score1	Score2	Score3	Max TotalScore
ClassA	grouped	grouped	240	85	95	80	260

My Splunk Search

| index=scoreindex | stats values(Name) as Name, values(Subject) as Subject, max(TotalScore) as TotalScore, max(Score1) as Score1, max(Score2) as Score2, max(Score3) as Score3 by Class | table Class Name, Subject, Total Score, Score1, Score2, Score3

I think my search below is going to display the following.

Class	Name	Subject	TotalScore	Score1	Score2	Score3
ClassA	Name1 Name2 Name3	Math English	240	85	95	80

This is the whole data in table format from scoreindex

Class	Name	Subject	TotalScore	Score1	Score2	Score3
ClassA	Name1	Math	170	60	40	70
ClassA	Name1	English	195	85	60	50
ClassA	Name2	Math	175	50	60	65
ClassA	Name2	English	240	80	90	70
ClassA	Name3	Math	170	40	60	70
ClassA	Name3	English	230	55	95	80

Re: How to calculate total when aggregating using stats max(field)?

_JP — Wed, 11 Oct 2023 17:10:13 GMT

How is this different than what you asked here:

Solved: Re: How to display other fields on the same row wh... - Splunk Community

It's nearly the same question, and that linked post seems to have the answer to this post in it.

Re: How to calculate total when aggregating using stats max(field)?

richgalloway — Wed, 11 Oct 2023 17:11:50 GMT

You *think* your search will produce that output? Why not run the search and remove the doubt?

To calculate a total, use the sum function.

| index=scoreindex | stats values(Name) as Name, values(Subject) as Subject, sum(TotalScore) as TotalScore, max(Score1) as Score1, max(Score2) as Score2, max(Score3) as Score3, max(TotalScore) as "Max TotalScore" by Class | table Class, Name, Subject, TotalScore, Score1, Score2, Score3, "Max TotalScore"

Re: How to calculate total when aggregating using stats max(field)?

LearningGuy — Wed, 11 Oct 2023 17:23:47 GMT

Hello,

Sorry I made a mistake on my original post. I just updated my question
I am looking for Max Total Score, a total score after the aggregation
Please suggest. Thank you

Max Total Score = Max(Score1) + Max(Score2) + Max(Score3) = 85+95+80 = 260

This is the output I need

Class	Name	Subject	TotalScore	Score1	Score2	Score3	Max TotalScore
ClassA	grouped	grouped	240	85	95	80	260

Re: How to calculate total when aggregating using stats max(field)?

LearningGuy — Wed, 11 Oct 2023 17:26:18 GMT

Hello,

Sorry I made a mistake on my original post. I just updated my question
I am looking for Max Total Score, a total score after the aggregation (260), before the aggregation Max(TotalScore) is 240 only for 1 row.
Please suggest. Thank you

Max Total Score = Max(Score1) + Max(Score2) + Max(Score3) = 85+95+80 = 260

This is the output I need

Class	Name	Subject	TotalScore	Score1	Score2	Score3	Max TotalScore
ClassA	grouped	grouped	240	85	95	80	260

Re: How to calculate total when aggregating using stats max(field)?

richgalloway — Wed, 11 Oct 2023 18:14:50 GMT

Something like that can be done using eval.

| index=scoreindex | stats values(Name) as Name, values(Subject) as Subject, sum(TotalScore) as TotalScore, max(Score1) as Score1, max(Score2) as Score2, max(Score3) as Score3 by Class | eval "Max TotalScore"=Score1 + Score2 + Score3 | table Class, Name, Subject, TotalScore, Score1, Score2, Score3, "Max TotalScore"

Re: How to calculate total when aggregating using stats max(field)?

LearningGuy — Wed, 11 Oct 2023 19:09:47 GMT

I tested your suggestion and it worked even on real data with multiple "Classes" (Class A, B, C).
I thought eval would not work after passing "stats" pipe, so I tried to sum (Score1+Score2+Score3) within the stats, but it would not let me. I accepted this as a solution.
Could you give an explanation why it worked after passing "stats" function?
Thank you so much

Re: How to calculate total when aggregating using stats max(field)?

richgalloway — Wed, 11 Oct 2023 19:18:12 GMT

I'm curious about why you thought eval would not work after stats.

There's nothing particularly magical about stats. It's a transforming command so only the fields used in the command are available to later commands. They are still fields, however, and can be processed as such. Note that some stats functions produce multi-value fields, which don't work well in all commands so they may require additional processing.