with t(id,amount) as( select '2012A',3 from dual union all select '2012B',4 from dual union all select '2011B',3 from dual union all select '2011C',3 from dual ) select id,(case when rn=1 then sum(amount) over(partition by substr(id,1,4)) else 0 end) amount from (select row_number() over(partition by substr(id,1,4) order by id) rn,id,amount from t); /* ID AMOUNT ---------- ---------- 2011B 6 2011C 0 2012A 7 2012B 0 */
with t(id,amount) as(
select '2012A',3 from dual
union all select '2012B',4 from dual
union all select '2011B',3 from dual
union all select '2011C',3 from dual
)
select id,(case when rn=1 then sum(amount) over(partition by substr(id,1,4)) else 0 end) amount
from (select row_number() over(partition by substr(id,1,4) order by id) rn,id,amount from t);
/*
ID AMOUNT
---------- ----------
2011B 6
2011C 0
2012A 7
2012B 0
*/