overall sum and aggregate sum

[yuming1127]

command:

search....

| eval effort=exact(21+31+61+1103+7306+7505+15105+15106+15122)
| table tag,effort,16910,21,31,61,1103,7306,7505,15105,15106,15122

how can i get effort = sum of field in same row instead of overall sum.

expected output:

effort

4

2

2

4

4

4

[bowesmana]

@yuming1127 

There's something odd in your SPL

In the eval line you are adding the following numbers together

| eval effort=exact(21+31+61+1103+7306+7505+15105+15106+15122)

so here effort evaluates to the number 61360

BUT in this statement 

| table tag,effort,16910,21,31,61,1103,7306,7505,15105,15106,15122

you are using these 'numbers' as fields and in your image they are actually field names containing only numbers.

So, the problem is that your eval statement is wrong in that it is adding up numbers not fields. In order to treat those fields as fields not numbers, you need to wrap them in single quotes, '. So your eval should be

| eval effort=('21'+'31'+'61'+'1103'+'7306'+'7505'+'15105'+'15106'+'15122')

BUT that will not give you your result for all rows, as the '21' field in row 1 in your example has no value, so will make 'effort' have no value.

You should use addtotals as @scelikok suggests and that will handle ALL fields or just the ones you specify and also handle the null value case.

[yuming1127]

ya, found a way to replace the null value with 0 and follow up with eval function, that worked too. Thanks!

[scelikok]

Hi @yuming1127,

You can use addtotals command;

| eval effort=exact(21+31+61+1103+7306+7505+15105+15106+15122)
| table tag,effort,16910,21,31,61,1103,7306,7505,15105,15106,15122
| addtotals

[yuming1127]

Great one, really appreciate your solution. Quick and easy