Change WM_CONCAT function to LISTAGG

I used WM_CONCAT in my sql query. The database has been updated to version 12c and WM_CONCAT doesn`t work.

Query had value:

Select r.product_id, count (1) how_many, wm_concat (distinct r.number) numbers From ...

How to transform it into LISTAGG functions. I try this, but it doesn`t work :

Select r.product_id, count(1) how_many, LISTAGG(r.number, ',') WITHIN GROUP (order by r.number) numbers From ... 

Answer

you should add Group by in your query

with tab as(
  select '10' as num, 'A' as product from dual union all
  select '10' as num, 'B' as product from dual union all
  select '10' as num, 'C' as product from dual union all
  select '10' as num, 'D' as product from dual union all
  select '20' as num, 'A' as product from dual union all
  select '20' as num, 'B' as product from dual union all
  select '30' as num, 'A' as product from dual
)

select  product
, count (1) how_many
, LISTAGG(num, ',') WITHIN GROUP (order by num) 
from tab
group by product

but you have to be careful, because by using listagg you can only concatenate strings up to 4K. There are also other string aggregation techniques, have a look at the answer here.

PRODUCT | HOW_MANY | LISTAGG(NUM,',')WITHINGROUP(ORDERBYNUM)
:------ | -------: | :--------------------------------------
A       |        3 | 10,20,30                               
B       |        2 | 10,20                                  
C       |        1 | 10                                     
D       |        1 | 10                                     

db<>fiddle here