How to edit my search to find the top 5 distinct count and the total?

[splunkrocks2014]

Assuming I have a lookup table with movie title and location, and I got the top 5 location based on distinct title count from the following search. What is the best way to get the top 5 distinct title count and the total from a search? Thanks.

| inputlookup movies.csv | stats dc(title) as count by location | sort limit=5 -count 

[aaraneta_splunk]

@splunkrocks2014 - Looks like you have a few possible solutions to your question. If one of them provided a working solution, please don't forget to click "Accept" below the best answer to resolve this post. If you still need help, please leave a comment. Don’t forget to upvote anything that was helpful too. Thanks!

[woodcock]

Like this:

| inputlookup movies.csv | eventstats dc(title) AS TotalTitles | stats first(TotalTitles) AS TotalTitles dc(title) AS count BY location | sort 5 -count | eval count = count . "/" . TotalTitles | fields - TotalTitles

[somesoni2]

Try like this

| inputlookup movies.csv | stats dc(title) as count by location | eventstats sum(count) as total | sort limit=5 -count 

[adayton20]

Are you simply trying to sum a total count from all the distinct title counts? Ie, adding together all counts in the column? You could try using addcoltotals: https://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/Addcoltotals

If that doesn't answer your question, could you describe a little further what you're looking for?

[splunkrocks2014]

If the query doesn't calculate the unique count, "addcoltotals" does all the work, for instance,

| inputlookup movies.csv | stats count(title) as count by location | sort limit=5 -count | addcoltotals

However, when used in the distinct query, "addcoltotals" adds up all numbers from dc(Title) which is incorrect.

[gcusello]

Hi splunkrocks2014,
what is your question? your search seems to be correct.
Bye.
Giuseppe