How to add multiple fields count values

[cooperjaram]

Hello,

I have 6 fields that I would like to count and then add all the count values together.

For example I have Survey_Question1, I stats count by that field which produces.

(NULL) 5630
1 2
3 4
4 24
5 558

The 1, 2, 3, 4, 5 are the rating given on the survey.

I have Survey_Question1-Survey_Questions6 I would like to get all their count values and then add them together so then I can divide by 6 and get an average, that I can then visualize onto a dashboard.

Hope this makes sense!

[Anantha123 · Accepted solution]

Please try below method.

basesearch field="Survey_Question1"
| stats count as Count1
| appendcols
[ search basesearch field="Survey_Question2"
| stats count as Count2 ]
| appendcols
[ search basesearch field="Survey_Question3"
| stats count as Count3 ]
| appendcols
[ search basesearch field="Survey_Question4"
| stats count as Count4 ]
| appendcols
[ search basesearch field="Survey_Question5"
| stats count as Count5 ]
| appendcols
[ search basesearch field="Survey_Question6"
| stats count as Count6 ]
| table Count1,Count2,Count3,Count4,Count5,Count6
| eval TotalCount=round(((Count1+Count2+Count3+Count4+Count5+Count6 )/2),2)
| table TotalCount

[cooperjaram]

This worked, thank you!

[Anantha123]

Try below

| stats count as Total , count(eval(field="Survey_Question1") ) as Count1 , count(eval(field="Survey_Question2") ) as Count2 ,count(eval(field="Survey_Question3") ) as Count3 , count(eval(field="Survey_Question4") ) as Count4 , count(eval(field="Survey_Question5") ) as Count5 ,count(eval(field="Survey_Question6") ) as Count6
| eval TotalCount=round(((Count1+Count2+Count3+Count4+Count5+Count6 )/6),2)

[cooperjaram]

When I attempt this I get the below error.

Error in 'stats' command: The dynamically evaluated field specifier 'eval(field="Survey_Question1") ' is invalid. The field specifier must be non-empty, start with '{', and end with '}'.

[jacobpevans]

Try this:

| stats count as Total ,
        sum(eval(field="Survey_Question1") ) as Count1,
        sum(eval(field="Survey_Question2") ) as Count2,
        sum(eval(field="Survey_Question3") ) as Count3, 
        sum(eval(field="Survey_Question4") ) as Count4, 
        sum(eval(field="Survey_Question5") ) as Count5,
        sum(eval(field="Survey_Question6") ) as Count6
| eval TotalCount=round(((Count1+Count2+Count3+Count4+Count5+Count6 )/6),2)

[cooperjaram]

I get the same error when I attempt this.

Error in 'stats' command: The dynamically evaluated field specifier 'eval(field="Survey_Question1") ' is invalid. The field specifier must be non-empty, start with '{', and end with '}'.

[jacobpevans]

Hm. I assume you're replaying field with your actual field name, correct? Apologies for the dumb question, just want to make sure we aren't missing anything easy.