如何确定组中计数最高的类别？

Question

select substr(shopabb, 1, 4) as shopgroup, count(*)
from table
where shopabb like'h%'
group by substr(shoppabb, 1, 4)
order by count(*) DESC;

我正在按子字符串组计算表中所有的商店缩写。我有另一个名为city的专栏，显示了商店缩写的位置。现在，我还想选择/显示子字符串组最常出现的城市，如果可能，还想显示该城市的shopabb计数。结果应该是这样的：

    shopabb   Count(*)   City      Count_City
    -----------------------------------------
   hel         50         London    40
   heal        20         Berlin    15   
   hot         10         Rome       8

谢谢！

Answer 1

使用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;

这是一个数据库

编辑：

你可以在没有窗口功能的情况下做到这一点，但是它要麻烦得多：

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
                  );

请注意，如果两个城市并列为最大值，这将返回重复。它需要一点更多的工作来获得总的总数。