Row-wise-sum in sql server

Question

I have a table as follows: Table1

Col1    Col2   Col3
1        A      10
2        B      20
3        C      30
4        D      40
5        E      50

I want output as follows:

Output :

Col1    Col2    Col3    Row-Wise-Sum
1       A       10      10
2       B       20      30
3       C       30      60
4       D       40      100
5       E       50      150

Please clarify what you want, just posting an example does not suffice. — Marian Theisen, Apr 09 '14 at 08:29
And is there any auto increment id? If so, you could fetch prev rows sum and add up with the present one. Please elaborate the problem — Karthikeyan, Apr 09 '14 at 08:30
@Tim Schmelter Thanks for the link. I am looking for that solution. — Venkateswarlu Avula, Apr 09 '14 at 08:33

Dan · Answer 1 · 2014-04-09T08:58:08.263

1

This is also known as a "running total" or "cumulative sum". If you search for any of those terms in combination with "SQL" you will find plenty of suggestions as to how this can be done.

Here's an answer that uses Window Functions in SQL Server 2012 or newer:

SELECT Col1, Col2, Col3,
    SUM(Col3) OVER (ORDER BY Col1 ROWS UNBOUNDED PRECEDING) AS [Row-Wise-Sum]
FROM
    MyTable
ORDER BY Col1

edited Apr 09 '14 at 08:58

answered Apr 09 '14 at 08:31

Dan

10,480
23
49

You should end the statement with ORDER BY Col1. – Thorsten Kettner Apr 09 '14 at 08:56

score 1 · Answer 2 · answered Apr 09 '14 at 08:33

1

Your talking a cumulative total

SELECT Table1.Col1, Table1.Col2, Table1.Col3, (SELECT SUM(Col3)
                       FROM Table1
                       WHERE Col1 <= Table1.Col1)
FROM   Table1
ORDER BY Table1.Col1;

answered Apr 09 '14 at 08:33

Eirwin

63
2

Row-wise-sum in sql server

2 Answers2