6-9 レコード数上位2件/その他/全体を出力

SQLパズル

testテーブル
 name
-----
name1
name1
name1
name2
name3
name3
name3
name3
name4
name4
name4
name4
name4
name4
name5
name5

name別の件数の上位2件(dense_Rankで順位付け)と、その他、全体を出力する。

出力結果
name     カウント
------   --------
name4           6
name3           4
その他          6
合計           16

SQL

with test as(
select 'name1' as name from dual
union all select 'name1' from dual
union all select 'name1' from dual
union all select 'name2' from dual
union all select 'name3' from dual
union all select 'name3' from dual
union all select 'name3' from dual
union all select 'name3' from dual
union all select 'name4' from dual
union all select 'name4' from dual
union all select 'name4' from dual
union all select 'name4' from dual
union all select 'name4' from dual
union all select 'name4' from dual
union all select 'name5' from dual
union all select 'name5' from dual)
select decode(grouping(name),1,'合計',name) as name,
sum(カウント) as カウント
from (select count(*) as カウント,
      case when dense_Rank() over(order by count(*) desc) > 2
           then 'その他' else name end as name
        from test
      group by name) a
group by rollup(name)
order by grouping(a.name),decode(a.name,'その他',1,0),カウント desc;

解説

dense_Rank関数で、
count(*)の降順に順位を付けてます。

---