How to count the same values over different fields together?

[RonWonkers]

Lets say we have the following data set:

Fruit_ID Fruit_1 Fruit_2

1 Apple NULL

2 Apple NULL

3 Apple NULL

4 Orange NULL

5 Orange NULL

6 Orange NULL

7 Apple Orange

8 Apple Orange

9 Apple Orange

10 Apple Orange

Now I am trying to count the total amount of every fruit, in the above example it should be 7 apples and 7 oranges, the problem is that these fruits are seperated in 2 different columns because a fruit name can be both an apple AND an orange, how do I deal with this when counting the total amount of fruit?

Counting one at a time works:

| stats count by Fruit_1

But how do I count both to give a total number since they are 2 seperate columns

I tried combining both columns so its all in 1 long list of values in 1 column but I could not get a definitive answer on how to do this. I tried appending results so first count Fruit_1, then append count Fruit_2 but I did not get the right result of Apple: 7 Orange: 7. Its either 1 or the other.

Does anybody have a fix for how to count over multiple fields like this and combine the result together in 1 field?

[ITWhisperer · Accepted solution]

| eval fruit=mvappend(fruit1,if(fruit2!="NULL",fruit2,null()))
| stats count by fruit

[RonWonkers]

Thank you! Just like that it works and only in 1 line 🙂

[RonWonkers]

A little update:

I now got to a point where I have the following situation:

Fruit_1 count Fruit_2 count

Apple 5 null null

Orange 10 null null

Pear 5 Apple 5

Melon 10 Orange 10

How do I get it so that the amount of apples and oranges from column Fruit_1 and Fruit_2 are combined into 1 big fruit list and 1 count list?

So the result should be:

Fruit Count

Apple 10

Orange 20

Pear 5

Melon 5

[RonWonkers]

For context: this question is regarding use cases/user stories for Splunk.

A use case can be linked to multiple user stories, and I want to count the amount of total user stories.