Help creating multiple SUMs within a field based on the value of another field

[raysonjoberts]

I am using 2 lookup tables to correlate and combine data to create a new .csv. In this process, I have a field that has numerical values in it that i want to sum based on the values of another field.  The raw data looks something like this:

stage,resource,hours
x,rick,1
x,rick,10
x,dave,1
y,rick,5
y,dave,3
y,dave,8

I want the output to look like:
x,rick,11
x,dave,1
y,rick,5
y,dave,11

Below is the search I have, it almost works but it does not sum the totals for rick and dave individually, it sums them all, so the output looks like:

x,rick,12
x,dave,12
y,rick,16
y,dave,16

|inputlookup mod_master
|lookup lookuptable1 Engagement OUTPUTNEW ResourceLastName,RegularHours
| eval Resource=mvdedup(ResourceLastName)
| mvexpand Resource
| eval Hours=sum(RegularHours)
| fillnull value=0 Hours
| table Stage,Resource,Hours

How can i change the logic in this search so I get the correct individual sums for Rick and Dave and not the combined total for each?

Thank you in advance!

[tshah-splunk]

Hey @raysonjoberts,

Can you try replacing the eval function with the below line in the query please?

| stats sum(hours) as hours by stage resource

 This will do a total of hours and group it w.r.t resource and the stage.

[raysonjoberts]

Thanks @tshah-splunk , but I am not only trying to sum(Hours) by stage,resource.

I am creating a table which has multiple fields in it and within that table, I am trying to sum(hours) by stage,resource. I simplified my search in my original question but the table includes multiple fields from each of the lookups I reference in the search. In other words, I am not looking to just add the hours, I want to represent the sum of those hours within a new table.

[PickleRick]

Either you're trying to do something very confusing 😄 or you can just use eventstats instead of stats to get your summarized values along the original data so you can process it further.

[bowesmana]

Can you share your data - the stats command given by @tshah-splunk would work on what you describe as your raw data, so your data is different - can you show what the data looks like after this

|inputlookup mod_master
|lookup lookuptable1 Engagement OUTPUTNEW ResourceLastName,RegularHours