Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table mytable(accountnumber,org_id) as (
select 4435354, 81  from dual union all
select 4435354, 281 from dual union all
select 4435354, 404 from dual union all
select 3333333, 81  from dual union all
select 3333333, 281 from dual union all
select 4444444, 81  from dual union all
select 4444444, 81  from dual union all
select 4444444, 281 from dual union all
select 4444444, 404 from dual
);
9 rows affected

select
  accountnumber
  ,count(org_id) cnt
  ,count(distinct org_id) cnt_distinct
  ,listagg(org_id,',')within group(order by org_id) orgs
  ,listagg(distinct org_id,',')within group(order by org_id) orgs_distinct
from mytable
group by accountnumber;


ACCOUNTNUMBER
CNT
CNT_DISTINCT
ORGS
ORGS_DISTINCT


3333333
2
2
81,281
81,281

4435354
3
3
81,281,404
81,281,404

4444444
4
3
81,81,281,404
81,281,404


select
  accountnumber
  ,org_id
  ,count(org_id)          over(partition by accountnumber) cnt
  ,count(distinct org_id) over(partition by accountnumber) cnt_distinct
  ,listagg(org_id,',')within group(order by org_id)
     over(partition by accountnumber)
       as orgs
  ,listagg(distinct org_id,',')within group(order by org_id) 
     over(partition by accountnumber)
       as orgs_distinct
from mytable;


ACCOUNTNUMBER
ORG_ID
CNT
CNT_DISTINCT
ORGS
ORGS_DISTINCT


3333333
81
2
2
81,281
81,281

3333333
281
2
2
81,281
81,281

4435354
81
3
3
81,281,404
81,281,404

4435354
281
3
3
81,281,404
81,281,404

4435354
404
3
3
81,281,404
81,281,404

4444444
81
4
3
81,81,281,404
81,281,404

4444444
81
4
3
81,81,281,404
81,281,404

4444444
281
4
3
81,81,281,404
81,281,404

4444444
404
4
3
81,81,281,404
81,281,404

ACCOUNTNUMBER	CNT	CNT_DISTINCT	ORGS	ORGS_DISTINCT
3333333	2	2	81,281	81,281
4435354	3	3	81,281,404	81,281,404
4444444	4	3	81,81,281,404	81,281,404