I don't know why DISTINCT doesn't work in PL/SQL. You might need to do two GROUP BYs; one to get distinct branches, and the other to collapse that down to one row per deptno:
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 ;
Don't use wm_concat; it's not documented. Since you have Oracle 11.2, you can use LISTAGG, or, if you really need the DISTINCT feature outside of PL/SQL, the user-defined STRAGG which you can copy from the following page:
I wouldn't discourage someone from using WM_CONCAT if they have not yet moved to 11gR2.
While not documented at tahiti ... it is most certainly documented by Tom Kyte:
and all of the other WM_ functions are documented:
If in 11gR2 then use LISTAGG
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