Subgroup rows for addcoltotals

[SarahWKarvenz]

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!

[SarahWKarvenz]

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 ]