I would like to use the add column totals to get the sum of certain rows. Is there a way to specify a "by" clause in the addcoltotals? or a way to choose which rows to add into the data?
Here is my example data:
CITY STATE # CATS # DOGS
CityA TX 1 5
CityB TX 2 4
CityC TX 4 2
CityD OK 2 2
CityE OK 5 6
I would like the output to show me the column totals by State and then also have a total of the entire dataset. The output would give me something like:
CITY STATE # CATS # DOGS
CityA TX 1 5
CityB TX 2 4
CityC TX 4 2
TX 7 11
CityD OK 2 2
CityE OK 5 6
OK 7 8
14 19
I can easily get the last row which has totals for the entire data set by using addcoltotals. But how can I get the totals by state?
Thanks!
I have answered my own question....the key is to use the appendpipe option. The original query would look something like this:
index=cats_dogs | stats count(cats) as CATS, count(dogs) as DOGS by CITY, STATE
when wanting the subgroups in there, the query becomes:
index=cats_dogs | stats count(cats) as CATS, count(dogs) as DOGS by CITY, STATE | appendpipe [ stats sum(CATS) as CATS, sum(DOGS) as DOGS by STATE ]