topic Re: How to use 'group by' with two fields? in Splunk Search

How to use 'group by' with two fields?

harish_ka — Tue, 11 Nov 2014 15:17:30 GMT

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??

Re: How to use 'group by' with two fields?

harish_ka — Wed, 12 Nov 2014 14:16:21 GMT

Can anyone help me????

Re: How to use 'group by' with two fields?

aweitzman — Wed, 12 Nov 2014 15:01:51 GMT

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?

Re: How to use 'group by' with two fields?

harish_ka — Wed, 12 Nov 2014 15:23:38 GMT

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

Re: How to use 'group by' with two fields?

aweitzman — Wed, 12 Nov 2014 16:26:13 GMT

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.

Re: How to use 'group by' with two fields?

aljohnson_splun — Wed, 12 Nov 2014 17:15:09 GMT

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

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

Re: How to use 'group by' with two fields?

harish_ka — Thu, 13 Nov 2014 09:41:39 GMT

it worked, Thanks.

Re: How to use 'group by' with two fields?

harish_ka — Thu, 13 Nov 2014 14:45:13 GMT

Thanks AlJohnson. It worked 🙂

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

Re: How to use 'group by' with two fields?

alcastic — Tue, 18 Jul 2017 16:22:13 GMT

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