How to use 'group by' with two fields?

[harish_ka]

I have 5 books. I have to show the count of these 5 books for different location.
I am getting the report like

Location             Book           Count
-------------      --------       ----------
NYC                  Book1            3
NYC                  Book2            5
.
.
.
Boston               Book1            22
Boston               Book2            99
.
.
Dallas               Book1            44
.
.

I have to generate report like below

Location             Book           Count
-------------      --------       ----------
NYC                  Book1            3
-----                Book2            5
-----                Book3            3
-----                Book4            1
-----                Book5            8

Boston               Book1            32
-----                Book2            51
-----                Book3            32
-----                Book4            11
-----                Book5            88

Dallas               Book1            13
-----                Book2            15
-----                Book3            13
-----                Book4            11
-----                Book5            18

Can anyone help me with the query??

[harish_ka]

it worked, Thanks.

[aljohnson_splun · Accepted solution]

It sounds like you need a nested stats, like this:

| stats count by book location
| sort count 
| stats list(book), list(count) by location

Breaking down the search

1. Get a count of books by location | stats count by book location, so now we have the values.
2. Then we sort by ascending count of books | sort count
3. Lastly, we list the book titles, then the count values separately by location |stats list(book), list(count) by location

[alcastic]

Thank you to much! I was useful for me too! but one question more: for each "Location", how i can get the "Book" with max "count"? Ex:

Location       Book       Count
-------------  --------   ----------
 NYC            Book5       3
 Boston         Book5       32
 Dallas         Book5       13

[harish_ka]

Thanks AlJohnson. It worked 🙂

This answer also helped me..
(http://answers.splunk.com/answers/73898/removing-duplicates-in-exported-report-results.html)

[harish_ka]

In the first report,
NYC Book1 3
NYC Book2 5

NYC are repeated in each row.I want report in which location are not repeated.
i used query as eval count as ABC by location,Book

[aweitzman]

That's not a valid search. I would have expected stats count as ABC by location, Book. And that search would return a column ABC, not Count as you've shown here.

Anyways, my best guess is that it will be difficult to do exactly what you're asking. You really shouldn't expend a lot of effort trying to make the search language change the presentation of tables. Presentation is what charts are for!

If you change your search to chart count by location, Book you'll get a result where one of each location is in the first column, plus one column for each book with the appropriate count. If you're just trying to compress the presentation, that might help.

Plus, you can easily turn this into a column or bar chart.

[harish_ka]

Can anyone help me????

[aweitzman]

I'm not sure I understand the question. I don't see a difference between the two reports except that the top one has fewer entries. What exactly is the difference that you are trying to accomplish?

Also, what is the search you are using to generate the top table?