How to get a total count and count by specific field displayed in the same stats table?

[matthewb4]

Let's say I have a base search query that contains the field 'myField'. I want to create a query that results in a table with total count and count per myField value. In addition, I want the percentage of (count per myField / totalCount) for each row. I want it to look like the following...

| myField | count | totalCount| percentage
| a       | 5     | 50        | .1
| b       | 10    | 50        | .2
| c       | 20    | 50        | .4
| d       | 5     | 50        | .1
| e       | 10    | 50        | .2

I'm trying to run something like...

base query | stats count as totalCount | stats count by myField | eval percentage=(count/totalCount)

Obviously this isn't right, but I don't know where to go from here. Any suggestions would be very appreciated. Thanks.

[somesoni2 · Accepted solution]

Give this a try

base search | stats count by myfield | eventstats sum(count) as totalCount  | eval percentage=(count/totalCount)

OR

base search | top limit=0 count by myfield showperc=t | eventstats sum(count) as totalCount

[matthewb4]

wow, that worked perfectly, thx. I was messing around with eventstats earlier and could not get this work. If you could explain the sum part to me it would be very helpful. As far as I am aware, 'eventstats' works off the original and unaltered set of data as if it was the first pipe command. I am slight confused on how sum(count) produces the correct results in this case.

[somesoni2]

The eventstats works on the dataset/result available to it (all result in whatever format available just before eventstats command is invoked), and without altering it, adds new information/column. So the data available before eventstats was the output of "stats count by myfield", which will give you one row per myfield with corresponding count. The eventstats is then summing all those count values and adding it as a new field to each row.

[somesoni2]

Easiest option to understand is the just run the command before eventstats and then add the eventstats to see the changes.

[matthewb4]

Ok I guess my question then is why does this work...
base search | stats count by myfield | eventstats sum(count) as totalCount
But this doesn't...
base search | stats count by myfield | stats sum(count) as totalCount

[somesoni2]

You're using stats command to calculate the totalCount which will summarize the results before that, so you'll only get a single row single column for totalCount. Your requirement was to keep the myfield and corresponding count, and get an additional field for totalCount (to calculate percentage) in each row, so eventstats is the way to go.

[matthewb4]

oh ok my understanding of eventstats was slightly off, your last comment makes a lot of sense now, thanks for all the help, this has been troubling me all day. You definitely cleared up my confusion!