10-180 集計結果で集計

SQLパズル

VisitedTable
Name
--------
Bill
Bob
Erica
Erica
Fred
Jack
Jack
Jack
Jack
Jim
Jim
Julie
Julie
Julie
Sam
Sam
Sam
Michelle
Michelle

Nameごとのレコード数を集計し、
peopleの降順、timesの昇順で出力する。

出力結果
VisitedInfo
------------------------
3 people visited 1 time
3 people visited 2 times
2 people visited 3 times
1 people visited 4 times

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table VisitedTable as
select 'Bill' as Name from dual
union all select 'Bob' from dual
union all select 'Erica' from dual
union all select 'Erica' from dual
union all select 'Fred' from dual
union all select 'Jack' from dual
union all select 'Jack' from dual
union all select 'Jack' from dual
union all select 'Jack' from dual
union all select 'Jim' from dual
union all select 'Jim' from dual
union all select 'Julie' from dual
union all select 'Julie' from dual
union all select 'Julie' from dual
union all select 'Sam' from dual
union all select 'Sam' from dual
union all select 'Sam' from dual
union all select 'Michelle' from dual
union all select 'Michelle' from dual;

SQL

col VisitedInfo for a30

--■■■分析関数を使う方法■■■
select distinct to_char(count(*) over(partition by count(*)))
      || 'people visited '
      || to_char(count(*))
      || case when count(*) = 1 then 'time'
              else 'times' end as VisitedInfo
  from VisitedTable
 group by Name;

--■■■インラインビューを使う方法■■■
select  to_char(count(*))
      || 'people visited '
      || to_char(preCount)
      || case when preCount = 1 then 'time'
              else 'times' end as VisitedInfo
from (select count(*) as preCount
        from VisitedTable
      group by Name)
group by preCount;

解説

この場合は、
インラインビューを使う方法がわかりやすいでしょうか。

---