SQL query to get grouped sums and concatenated lists, preferably without using functions

Question

I have a table like

colA    colB    colC
A        10      1
A        20      2
A        30      3
B        10      1
B        20      2

I want an output like this

ColA  colB  colC
A      60    1,2,3
B      30    1,2

Can someone tell me how to do it with and without using functions, and in PL/SQL?

Answer 1

Here is sql you needed. Note : it will only run on oracle 11g R2 onwards.

   with tab as( 
    select 'A' col1,10 col2,1 col3 from dual union all
    select 'A' col1,20 col2,2 col3 from dual union all
    select 'A' col1,30 col2,3 col3 from dual union all
    select 'B' col1,10 col2,1 col3 from dual union all
    select 'B' col1,20 col2,2 col3 from dual )
    select  col1, sum(col2),listagg(col3,',') WITHIN GROUP (ORDER BY col3) AS col3_list 
    from tab group by col1

Answer 2

Assume your table name is tab

with tab1(col1, col2) as (
  select colA, sum(colB) 
  from tab group by colA
),
tab2(col1, col2) as (
  select colA, LISTAGG(TO_CHAR(colC),',')
  WITHIN GROUP (ORDER BY colC)
  from tab group by colA
)
select a.col1, a.col2, b.col2 
from tab1 a, tab2 b 
where a.col1 = b.col1

OutPut:

COL1  COL2  COL2
A     60    1,2,3
B     30    1,2

For more information about LISTAGG link

This link explains about multiple with clause

Or simply;

select colA, sum(colB) colB,LISTAGG(TO_CHAR(colC),',')
  WITHIN GROUP (ORDER BY colC)
  from tab group by colA

Answer 3

Probably this may the easiest solution syntactically :)

SELECT COLA,
  SUM(COLB),
  WMSYS.WM_CONCAT(COLC)
FROM
  (SELECT 'A' cola,10 AS colb,1 AS colc FROM dual
  UNION
  SELECT 'A' cola,20 AS colb,2 AS colc FROM dual
  UNION
  SELECT 'A' cola,30 AS colb,3 AS colc FROM dual
  UNION
  SELECT 'B' cola,10 AS colb,1 AS colc FROM dual
  UNION
  SELECT 'B' cola,20 AS colb,2 AS colc FROM dual
  )
GROUP BY COLA ;

OUTPUT

COLA SUM(COLB) WMSYS.WM_CONCAT(COLC) A 60 1,2,3

B 30 1,2