select accessno,
(select sum(xp) from t1 where accessno=tt.accessno) as xp,
(select sum(sp) from t1 where accessno=tt.accessno) as sp,
optpye,
fail,
success
from t1 tt
(select sum(xp) from t1 where accessno=tt.accessno) as xp,
(select sum(sp) from t1 where accessno=tt.accessno) as sp,
optpye,
fail,
success
from t1 tt
t2.xp,
t2.sp,
optpye,
fail,
success
from t1,(select accessno,sum(xp) xp,sum(sp) from t1 group by accessno) t2
where t1.accessno=t2.accessno
sum(xp) over (partition by no order by no
rows between unbounded preceding and unbounded following)