2-3-3 集合関数のネスト

SQL

select max(count(Val1)) as "count(Val1)の最大値"
from (select 1 as Col1,2 as Val1 from dual
      union select 1,4 from dual
      union select 1,5 from dual
      union select 2,6 from dual
      union select 2,7 from dual)
group by Col1;

解説

集合関数は第二レベルまでネストすることが可能です。

distinctオプションも使えます。

SQL> select sum(max(Val1)) as "max(Val1)の合計値"
  2  from (select 1 as Col1,2 as Val1 from dual
  3        union select 1,4 from dual
  4        union select 1,5 from dual
  5        union select 2,6 from dual
  6        union select 2,7 from dual
  7        union select 3,5 from dual)
  8  group by Col1;

max(Val1)の合計値
-----------------
               17

SQL> select sum(distinct max(Val1)) as "max(Val1)の合計値"
  2  from (select 1 as Col1,2 as Val1 from dual
  3        union select 1,4 from dual
  4        union select 1,5 from dual
  5        union select 2,6 from dual
  6        union select 2,7 from dual
  7        union select 3,5 from dual)
  8  group by Col1;

max(Val1)の合計値
-----------------
               12