使用 row_number() 具有两个聚合级别：

select shopgroup, sum(cnt) as total_count, 
       max(case when seqnum = 1 then city end) as city_with_max,
       max(case when seqnum = 1 then cnt end) as cnt_at_max
from (select substr(shopabb, 1, 4) as shopgroup, city, count(*) as cnt,
             row_number() over (partition by substr(shopabb, 1, 4) order by count(*) desc) as seqnum
      from table
      where shopabb like'h%'
      group by substr(shopabb, 1, 4), city
     ) t
group by shopgroup
order by sum(cnt) DESC;

这里有一个db<>小提琴说明了语法的工作原理。
编辑：
您可以不使用窗口函数来执行此操作，但要复杂得多：

select substr(shopabb, 1, 4) as shopgroup, city, count(*) as cnt
from table t
where shopabb like'h%'
group by substr(shoppabb, 1, 4), city
having count(*) = (select count(*)
                   from table t2
                   where substr(t2.shopabb, 1, 4) = substr(t.shopabb, 1, 4)
                   group by city
                   order by count(*) desc
                   limit 1
                  );

请注意，如果两个城市的最大值相等，那么这将返回重复的结果。要得到总的结果还需要更多的工作。