summing up rows by two column

[ISP8055]

Hi there, 

I have a table with 5 fields. 
E column is numeric value, C is sub category of A

I want to sum E by column C AND column A.

A B C D E
a     x       30

a     y       20

a     x       40

b    y        10

b    x        40


if I do stats(sum E) by A = it will give output of sum of first three rows of E.
if I do stats(sum E) by c = it will give output of rows by x and by e. 
I want to output be like a x 50  ( 50 is 30 + 20 in this case)

Hope, I conveyed what I'm going for.

[Richfez · Accepted solution]

I think what you want is ...

| stats sum(E) by A, C

And that should give you your answer.  (I think you miscalculated your example.  ax should be 30+40=70 and is the first and third lines, right?).

The output I get is

A	C	sum(E)
a	x	70
a	y	20
b	x	40
b	y	10

And the run-anywhere search you can test with yourself was:

| makeresults 
| eval testData = "a,x,30 a,y,20 a,x,40 b,y,10 b,x,40"
| makemv delim=" " testData
| mvexpand testData
| makemv delim="," testData
| eval A = mvindex(testData, 0), C = mvindex(testData,1), E = mvindex(testData, 2)
| stats sum(E) by A, C

Happy Splunking!

-Rich

[ISP8055]

You are correct. it should be 70. Sorry, typo on my end.