Splunk Search

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

Builder

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

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
Labels (1)
• ### stats

1 Solution
Contributor

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.

Builder

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

SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
Builder

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.

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
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
Builder

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

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
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
Contributor

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.

Get Updates on the Splunk Community!

#### .conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

#### Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

#### Troubleshooting the OpenTelemetry Collector

In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...