Is there a faster way to calculate percentages faster?

[cindygibbs_08]

Well Hello Gorgeous people!

I have a fields that can take anywhere from 3 to 5 diferente values which are cities... this field is called "CITY" if I want to get the percentage of each city over the total count I always Have to do something like this:

| stats count(eval(CITY="A")) as CITY_A, count(eval(CITY="B")) as CITY_B, count(eval(CITY="C")) as CITY_C, count(CITY) as TOTAL
| eval %P_CITY_A=CITY_A/TOTAL (repeat for each city)

but often times I find myself wanting to calculate percetages of the values of fields that can have up to 15 differente values.. and I just wonder is there is a faster more effcient way of doing this.... thank you so much people

love,

cindy

[bowesmana · Accepted solution]

The top command does just that

| makeresults 
| eval CITY="Boston;Sydney;London;Cairo;Paris;Rome;Casablanca;Vancouver;San Francisco;Munich;Berlin;Helsinki" 
| eval CITY=split(CITY,";") 
| mvexpand CITY 
| eval n=mvrange(1,random() % 10 + 1)
| mvexpand n
| eval COMMENT="UP TO HERE PRODUCES EXAMPLE DATA"
| top 0 CITY

As for whether this is faster than doing eventstats the calculating percentages, I am not sure, but it's certainly less code

[richgalloway]

At first I thought about using FOREACH, but I'm sure the real city names don't all start with "CITY_" so that won't work.

This does work, however, but it's a little ugly.

| makeresults 
| eval CITY="Boston;Sydney;London;Boston;Paris;Cairo;Paris;London" 
| eval CITY=split(CITY,";") 
| mvexpand CITY 
```Everything above sets up test data.  Don't use in the real query```
```Get counts for each city```
| stats count by CITY 
```Get the total count and assign it to bogus city "A" (don't change that)```
```Get the total count and add it as a field to each row```
| eventstats sum(count) as TOTAL
```Compute the percentage```
| eval PCT=count*100/TOTAL 
```Remove the TOTAL field (optional)```
| fields - TOTAL