how to add the values as cummulative values in seperate column?

Question

COL COL1    COL2    SUM cumm
1   2   3   6    6
4   5   6   15   21 
7   8   9   24   45

In the above table result set i need the cumm column values as like this , how to do this with an query , can someone help?

In order for a running total to make sense, you need some way to determine in what *order* the values should be added. Is one of the columns you've shown the column that should be used to determine the order of the rows? If not, you need more columns. — Damien_The_Unbeliever, May 20 '13 at 07:38

score 3 · Answer 1 · answered May 20 '13 at 06:24

Try this one -

Query:

DECLARE @temp TABLE
(
      Col1 INT
    , Col2 INT
    , Col3 INT
    , Col4 INT
)

INSERT INTO @temp (Col1, Col2, Col3, Col4)
VALUES 
    (1,   2,   3,   6),
    (4,   5,   6,   15),
    (7,   8,   9,   24)

SELECT 
      Col1
    , Col2
    , Col3
    , Col4
    , SUM_cumm = SUM(Col4) OVER(
        ORDER BY Col4 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM @temp

Output:

Col1        Col2        Col3        Col4        SUM_cumm
----------- ----------- ----------- ----------- -----------
1           2           3           6           6
4           5           6           15          21
7           8           9           24          45

Are you showing the Results for **`SQL SERVER 2012`**. I think the OP needs the solution for **`SQL SERVER 2008`** — Prahalad Gaggar, May 20 '13 at 06:37

score 2 · Answer 2 · answered May 20 '13 at 07:01

You can use CTE

create table table1
(
    COL int,
    COL1 int,
    COL2 int,
    COL3 int
)
insert into table1
(COL,COL1,COL2,COL3)
values
(1,   2,   3,   6),
(4,   5,   6,   15), 
(7,   8,   9,   24)

select 
rank() OVER (ORDER BY COL3) as [Rank],
t1.COL,
t1.COL1,
t1.COL2,
t1.COL3
into #temp1
from table1 t1

;WITH x AS
(
    SELECT 
        [Rank],
        COL,
        COL1,
        COL2,
        COL3,
        Total=COL3
    FROM #temp1
    WHERE [Rank] = 1
    UNION ALL
    SELECT 
        y.[Rank],
        y.COL,
        y.COL1,
        y.COL2,
        y.COL3,
        x.Total+(y.COL3)
    FROM x INNER JOIN #temp1 AS y
    ON y.[Rank] = x.[Rank] + 1
)
SELECT 
    COL,
    COL1,
    COL2,
    COL3,
    Total
FROM x
OPTION (MAXRECURSION 100);

SQL FIDDLE

how to add the values as cummulative values in seperate column?

2 Answers2