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

Question

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

Ex : ColumnData

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.

This is called a "running total". I'm sure you can find out how to do it by googling that phrase. — Barmar, May 11 '15 at 16:48
Possible duplicate [Create a Cumulative Sum Column in MySQL](http://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql) — 54l3d, May 11 '15 at 16:51

score 1 · Answer 1 · answered May 11 '15 at 16:51

1

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

answered May 11 '15 at 16:51

jpw

44,361
6
66
86

I've never seen `sum() over(order by )` without a `range` or `rows` expression, like `sum() over(order by rows unbounded preceding)`. Is there a difference? They appear to have different execution plans. – Bacon Bits May 11 '15 at 19:37
@BaconBits The window frame is optional. The documentation says: "If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame." – jpw May 11 '15 at 19:42
Oh, I missed that. Thanks! – Bacon Bits May 11 '15 at 19:43

score 0 · Answer 2 · answered May 11 '15 at 17:19

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

The question is about MS SQL Server which doesn't support PL/SQL. — jpw, May 11 '15 at 19:44

score 0 · Answer 3 · answered Apr 10 '17 at 05:37

0

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

answered Apr 10 '17 at 05:37

ashish

239
2
6
13

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

3 Answers3