This is my data :
I want to group result by two fields like that :
I follow the instructions on this topic link text , but I did not get the fields grouped as I want. They are grouped but I don't have the count for each row.
Can anyone help me?
Give this a try
your base search giving fields Location, Book and Count | stats sum(Count) as Count by Location Book | stats list(Book) as Book list(Count) as Count by Location
Your data actually IS grouped the way you want. You just want to report it in such a way that the Location doesn't appear. So, here's one way you can mask the RealLocation with a display "location" by checking to see if the RealLocation is the same as the prior record, using the autoregress function.
This part just generates some test data-
| makeresults | eval mydata = "NYC,book1,3 NYC,book2,5 NYC,book3,3 Boston,book1,32 Boston,book2,51 Boston,book3,32 Dallas,book1,13 Dallas,book3,13 Dallas,book2,15" |makemv mydata| mvexpand mydata |makemv delim="," mydata | eval Location=mvindex(mydata,0), Book=mvindex(mydata,1), Count=mvindex(mydata,2) | table Location, Book, Count
This part sorts it and masks the RealLocation
| sort 0 Location, Book | autoregress Location | rename Location as RealLocation | eval Location=if(RealLocation==Location_p1,"-",RealLocation) | table Location Book Count RealLocation
With these results
Location Book Count RealLocation Boston book1 32 Boston - book2 51 Boston - book3 32 Boston Dallas book1 13 Dallas - book2 15 Dallas - book3 13 Dallas NYC book1 3 NYC - book2 5 NYC - book3 3 NYC
However, you might want to consider this format instead -
|chart sum(Count) over Book by Location
...which gives this result
Book Boston Dallas NYC book1 32 13 3 book2 51 15 5 book3 32 13 3
Thanks for your help.
This worked for me :
| stats sum(Count) as Count by Location Book
| stats list(Book) as Book list(Count) as Count by Location