CODEWITHSUNDEEP
sqlsql-server
Phani
Phani

Reputation: 93

Adding first two rows result as a second row then addition of first three rows result as a third row and so on

I have a table in sql serevr,which has one column and its storing integer values.

Ex : ColumnData

   100

   150

   20

   25

   300

Now by using this data i want the result as shown below.

columndata NewColumn

   100        100

   150        250

   20         270

   25         295

   300        595

so in the output newcolumn is added by the logic i.e first row data as firstrow,then first two rows addition result as appears in second row,then first three rows addition result as appears in third row like so on...

could any one please provide me the query how to get my result.

Thanks In Advance,

Phani Kumar.

Upvotes: 1

Views: 4704

Answers (3)

ashish
ashish

Reputation: 239

with sal as( select a.empid,salry,row_number() over(order by empid) rn from empmaster a) select a.empid,a.salry,b.salry,a.salry+b.salry from sal a left outer join sal b on a.rn = b.rn-1

Upvotes: 0

Inderdeep Singh
Inderdeep Singh

Reputation: 105

You need to use PL SQL to to do this. Alter the table to have a new field id to to sort and value2 having final result.

DECLARE
 l_last_sum       INTEGER := 0;

 CURSOR test_cur
 IS
      SELECT id,value
        FROM test
    ORDER BY id ASC;

 l_test   test_cur%ROWTYPE;
BEGIN
 OPEN test_cur;

 LOOP
    FETCH test_cur INTO l_test;
    EXIT WHEN test_cur%NOTFOUND;

    l_last_sum:=l_last_sum+l_test.value;
    update test set value2=l_last_sum where id=l_test.id;

 END LOOP;

 CLOSE test_cur;
END; 



  SQL> select * from test;                                                                                

    ID      VALUE     VALUE2                                                                        
 ---------- ---------- ----------                                                                        
     1        100        100                                                                        
     2         25        125                                                                        
     3         40        165

Upvotes: 0

jpw
jpw

Reputation: 44881

Assuming that you have a column that you can order the data by then you can compute a running total by either using a windowed aggregate function (this works in SQL Server 2012+) or a self join (which works in any version). If you don't have any column to order by then it can't be done in a deterministic way at all.

-- sample table:
create table t (id int identity(1,1), ColumnData int)
insert t values (100),(150),(20),(25),(300)

-- query 1 using windowed aggregate
select ColumnData, sum(ColumnData) over (order by id) as  NewColumn
from t order by id

-- query 2 using self-join    
select t1.ColumnData, sum(t2.ColumnData) as  NewColumn
from t t1
join t t2 on t2.id <= t1.id
group by t1.id, t1.ColumnData
order by t1.id

Sample SQL Fiddle

Upvotes: 1

Related Questions