Need Help with sum over two columns without subsearch

[PPape]

Hi Guys,

I need some help with a stats command.

Given is Data like this csv

Round,Player1,Player2,ScorePlayer1,ScorePlayer2
1,Harry,Tom,5,1
2,Eva,Mike,1,0
3,Harry,Eva,3,4
4,Mike,Tom,4,6
5,Tom,Harry,3,2
6,Mike,Eva,7,5
7,Eva,Tom,2,5

All I want to have is a stats command that gives me a table with the total amount of points for every player.
I managed to do it with a subsearch but this feels kind of weird... there has to be a better solution.

[sundareshr · Accepted solution]

Try this

base search here | eval player=player1.";".player2 | eval score=ScorePlayer1.";".ScorePlayer2 | makemv player delim=";" | makemv score  delim=";" | eval z=mvzip(player, score) | mvexpand z | rex field=z "(?<player>[^;]+);(?<score>.*)" | stats sum(score) as score by player

[PPape]

There is a little mistake. The mvzip command uses an comma as seperator so the semikolon in the rex command must be replaced with an comma. Than it works perfectly! Thank you again

[PPape]

Thank you very much. This worked and is faster than my subsearch.