Search notes:
Oracle SQL: nesting aggregate functions
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;
«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
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