You didn't post CREATE TABLE and INSERT statements for your tables, so I can't test it.
INSERT INTO tt1 (cnt1, deptno1, sumt, cbranch) WITH got_distinct_branch AS ( SELECT deptno , branch , COUNT (no) AS count_no , COUNT (deptno) AS count_deptno , SUM (tot) AS sum_tot FROM tt GROUP BY deptno , branch ) SELECT SUM (count_no) , SUM (count_deptno) , SUM (sum_tot) , LISTAGG (branch, ',') WITHIN GROUP (ORDER BY branch) FROM got_distinct_branch GROUP BY deptno ;
there I wrote in regards to wm_concat:
my suggestion is going to be consistent....
Never use undocumented stuff, it is subject to change, removal, broken-ness without recourse.
a) use stragg
b) write your own
c) use the connect by trick.
That was in 2007, in 2011 I would add:
d) use listagg