How do you find the top 5% of data in a table?

[asish_100]

I have a table that contains hours worked against each task.

Now i want to estimate the top 5% of the task(like if there are 100 tasks i want to display top 20 tasks which consumed maximum hours).

My query is given below

base search|stats sum(hours) by task,domain,group.

Please help me to estimate the top 5% of the task.

[sergeye]

Hi,

probably this is not a best solution but it works.
just add this to the end of your query:

base search|stats sum(hours) as hours by task,domain,group
| sort - hours 
| streamstats count
| eventstats count as max_count
| eval percent = round(exact(exact(count * 100) / max_count), 0)
| search percent <= 5
| fields - count, max_count, percent

PS as FrankVl said I missed the "sort" command,
so I edited this post to add it.

[FrankVl]

You probably would want to do a sort on the sum(hours) first.

[sergeye]

Hi FrankVl,

yea, I missed it.
So the query should be:
base search|stats sum(hours) as hours by task,domain,group
| sort - hours
| streamstats count
| eventstats count as max_count
| eval percent = round(exact(exact(count * 100) / max_count), 0)
| search percent <= 5
| fields - count, max_count, percent