How to have only common values for 2 fields in the same search?

[tjosm]

I have a table like this:

Shops Location Total
1. CK SF 1000
2. CK LA 877
3. CK NY 543
4. CK BOS 436
5. OG NY 2112
6. OG DET 100
7. OG ATL 876
8. OG LA 421

I want a table/report like this:
Shops Location Total
1. CK LA 877
2. CK NY 543
3. OG NY 2112
4. OG LA 421

Code I have:
|tstats count where index=us-shops Shops IN (CK OG) Location=* by Shops,Location |sort - count| streamstats count as rank by Shops
| where rank < 9|stats sum(count) by Shops,Location | rename sum(count) as Total

[Sukisen1981]

hi @tjosm
Your query is a bit confusing,given you requirements.
You need to have Total as a by clause in both stats and stream stats. You are loosing the field 'Total' and instead having sum of count as Total which will probably give you output as 1 /(just the unique count), be that as it may I kinda took your initial data in a csv and added this code.
| stats count by Shops,Location,Total
| sort - count| streamstats count as rank by Shops | where rank < 9|stats sum(count) by Shops,Location,Total | rename sum(count) as newtotal
| eventstats count as loc_count by Location
| where loc_count>1
| fields - newtotal, - loc_count

tstats has become stats since i took the first data in a csv

[Sukisen1981]

hi @tjosm

Please let us know if your issue has been resolved and accept the answer if it significantly helped your resolution. Do not forget to add additional resolution details for the benefit of other form members.