Search notes:

Oracle SQL: nesting aggregate functions

Trying to demonstrate how aggregate functions can be nested

Table with test data:

create table tq84_nested_aggr_func_test (
   val_1  varchar2(5),
   val_2  number
);

insert into tq84_nested_aggr_func_test values ('ABC',  1);
insert into tq84_nested_aggr_func_test values ('ABC',  2);
insert into tq84_nested_aggr_func_test values ('ABC',  3);

insert into tq84_nested_aggr_func_test values ('DEF',  6);
insert into tq84_nested_aggr_func_test values ('DEF',  8);

insert into tq84_nested_aggr_func_test values ('GHI', 10);

commit;

Github repository Oracle-patterns, path: /SQL/select/aggregate/nested/create-table.sql

«Normal» group by with aggregation:

select
   val_1      val_1    ,
   avg(val_2) val_2_avg,
   sum(val_2) val_2_sum
from
   tq84_nested_aggr_func_test
group by
   val_1;
--
-- VAL_1  VAL_2_AVG  VAL_2_SUM
-- ----- ---------- ----------
-- GHI           10         10
-- DEF            7         14
-- ABC            2          6

Github repository Oracle-patterns, path: /SQL/select/aggregate/nested/select.sql

Aggregate aggregated numbers, resulting in one record:

select
   sum(avg(val_2)) val_2_sum_of_avg,
   avg(sum(val_2)) val_2_avg_of_sum
from
   tq84_nested_aggr_func_test
group by
   val_1
;
--
-- VAL_2_SUM_OF_AVG VAL_2_AVG_OF_SUM
-- ---------------- ----------------
--               19               10

Github repository Oracle-patterns, path: /SQL/select/aggregate/nested/select-nested.sql

Index