How to write stats query to find top 20 count records based on zipcode and split by gender?

[dhavamanis]

Can you please tell us how to write stats query for this case?

We have columns:

zipcode  gender

07809      f
07809     null
09331      m
09331      m
98567      m
98567      m
98567      m
98567      f
98567     null

We need a final stats output like below (top 20 records only based on zipcode and split by gender, zipcode wise highest count on top):

zipcode    gender    count
98567        m         3
98567        f         1
98567       null       1
09331        m         2
07809        f         1
07809       null       1

also need a column chart query: chart count over zipcode by gender

[_d_ · Accepted solution]

This should probably work:

| stats count by zipcode gender | eventstats sum(count) as cz by zipcode | sort 20 -cz 

or the chart version:

| stats count by zipcode gender | eventstats sum(count) as cz by zipcode | sort 20 -cz | chart max(count) over zipcode by gender | fillnull value=0

[skawasaki_splun]

... | stats count by zipcode gender | eventstats sum(count) as count_zipcode by zipcode | sort 0 -count_zipcode -count | streamstats dc(index) as rank_zipcode | where rank_zipcode<=20 | fields - count_zipcode rank_zipcode

For people who want to test a similar search on any Splunk instance:

| tstats count where index=* OR index=_* by index sourcetype | eventstats sum(count) as count_index by index | sort 0 -count_index -count | streamstats dc(index) as rank | where rank<=3 | fields - count_index rank