Splunk Search
Highlighted

## How to sum the count of two fields when they have the same value?

Contributor

I have data where each event has two fields to show the source and destination city of a package.I can get the count of each city separately, but am struggling to show a combined count.

`index=foo | stats count by destCity` -- returns a count of events for each destination city.

``````destCity             count
Baltimore            5
Philli               3
Toronto             20
``````

`index=foo | stats count by sourceCity` -- returns the count of events for each source city.

``````sourceCity       count
Atlanta                7
Baltimore            5
Toronto            15
``````

I need to be able to calculate:

``````country         count
Atlanta              7
Baltimore       10
Philli                  3
Toronto         35
``````
Tags (5)
1 Solution
Highlighted

## Re: How to sum the count of two fields when they have the same value?

SplunkTrust

I think this is as simple as

``````| eval city=mvappend(sourceCity,destCity) | stats count by city
``````

Whether a given event has both fields, or has just one or the other, it will still work.

Highlighted

## Re: How to sum the count of two fields when they have the same value?

Motivator

Try this,

``````index=foo | eval country=coalesce(destCity, sourceCity) | stats count by country
``````

Thanks,
V

Highlighted

## Re: How to sum the count of two fields when they have the same value?

SplunkTrust

Note this will not work properly whenever an event has both destCity and sourceCity fields - in such cases it will disregard that event's sourceCity field.

Highlighted

## Re: How to sum the count of two fields when they have the same value?

Contributor

I found that when I tried it. Most of my events have both fields. The mvappend worked beautifully!

Highlighted

Motivator

Perfect.. 🙂