stats table with individual count and a total count for two fields

[RecoMark0]

Hello,
Say I wanted to create a table with the fields State, City, City Count, and Total. If I try to use |stats values(city) as city, count by State
I get a chart that only lists cities, but does not give their individual total, only the total for State.

Is it possible to get my chart to look like this:

| STATE |      CITY       | city_count|     Total    |
|CA     |   San Jose      |    5      |     15      |
|       | Santa Clara     |    10     |             |

the last table in the answer here shows it better: https://answers.splunk.com/answers/97676/getting-counts-on-multiple-fields-while-grouping-by-one-of-... I don't have an "mcount" field, so is this still possible to get the count of each city there? My table would have srcip as State, malware_type as city, and mcount as the total count of each city.

Also, I tried using the solution using chart shown here: https://answers.splunk.com/answers/215808/how-to-get-stats-count-results-broken-down-per-fie.html?ut...

however that table is a lot harder to follow(it lists each city as its own column).

Thank you.

[somesoni2 · Accepted solution]

Here is how you will get the expected output

your base search | stats count by state city  | stats values(city) as city values(count) as city_count sum(count) as Total by State

[RecoMark0]

Wow, you are awesome. That worked perfectly. Thank you so much!