Monday, 11 April 2011

Sum of previous rows value for every row level

Name     age
Mohan    10
ramu       20
krishna    25
x             40
y             10
Now i want the result like
Name     age     Total
Mohan    10          10 (age value of this row + previous row age value)
ramu       20          30 (age value of this row + previous row age value)
krishna    25          55 (age value of this row + previous row age value)
x             40          95 ( age value of this row + previous row age value)
y             10         105 ( age value of this row + previous row age value)

Solution:
create table cd (ename varchar(10), age int) 
select * from cd 
insert into cd values ('mohan', 10)
 insert into cd values ('ramu', 20) 
insert into cd values ('krishna', 25)
 insert into cd values ('x', 40) 
insert into cd values ('y', 10) 
with cte 
as(Select row_number ()over (order by (select 0) ) as row_id,ename ,age from cd) 
select e.ename,e.age,sum(b.age) as total_sum,e.row_id from cte e
join cte b on e.row_id>=b.row_id 
group by e.ename,e.age,e.row_id 
order by e.row_id

No comments:

Post a Comment