How to get cumulative sum

Without using any type of JOIN cumulative salary for a person fetch by using follow query:

SELECT * , (
SELECT SUM( salary )
FROM `abc` AS table1
WHERE table1.ID <= `abc`.ID
AND table1.name = `abc`.Name
) AS cum
FROM `abc`
ORDER BY Name

The latest version of SQL Server (2012) permits the following.

SELECT
RowID,
Col1,
SUM(Col1) OVER(ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
FROM tablehh
ORDER BY RowId

or

SELECT
GroupID,
RowID,
Col1,
SUM(Col1) OVER(PARTITION BY GroupID ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
FROM tablehh
ORDER BY RowId

This is even faster. Partitioned version completes in 34 seconds over 5 million rows for me.

Thanks to Peso, who commented on the SQL Team thread referred to in another answer.

You can use this simple query for progressive calculation :

select
id
,SomeNumt
,sum(SomeNumt) over(order by id ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as CumSrome
from @t

For Ex: IF you have a table with two columns one is ID and second is number and wants to find out the cumulative sum.


SELECT ID,Number,SUM(Number)OVER(ORDER BY ID) FROM T

```lang-sql
select t1.id, t1.SomeNumt, SUM(t2.SomeNumt) as sum
from @t t1
inner join @t t2 on t1.id >= t2.id
group by t1.id, t1.SomeNumt
order by t1.id
```

[SQL Fiddle example][1]

**Output**

| ID | SOMENUMT | SUM |
-----------------------
| 1 | 10 | 10 |
| 2 | 12 | 22 |
| 3 | 3 | 25 |
| 4 | 15 | 40 |
| 5 | 23 | 63 |

[1]:

[To see links please register here]

**Edit:** this is a generalized solution that will work across most db platforms. When there is a better solution available for your specific platform (e.g., gareth's), use it!

Let's first create a table with dummy data:

Create Table CUMULATIVESUM (id tinyint , SomeValue tinyint)

Now let's insert some data into the table;

Insert Into CUMULATIVESUM
Select 1, 10 union
Select 2, 2 union
Select 3, 6 union
Select 4, 10

Here I am joining same table (self joining)

Select c1.ID, c1.SomeValue, c2.SomeValue
From CumulativeSum c1, CumulativeSum c2
Where c1.id >= c2.ID
Order By c1.id Asc

Result:

ID SomeValue SomeValue
-------------------------
1 10 10
2 2 10
2 2 2
3 6 10
3 6 2
3 6 6
4 10 10
4 10 2
4 10 6
4 10 10

Here we go now just sum the Somevalue of t2 and we`ll get the answer:

Select c1.ID, c1.SomeValue, Sum(c2.SomeValue) CumulativeSumValue
From CumulativeSum c1, CumulativeSum c2
Where c1.id >= c2.ID
Group By c1.ID, c1.SomeValue
Order By c1.id Asc

For SQL Server 2012 and above (much better performance):

Select
c1.ID, c1.SomeValue,
Sum (SomeValue) Over (Order By c1.ID )
From CumulativeSum c1
Order By c1.id Asc

Desired result:

ID SomeValue CumlativeSumValue
---------------------------------
1 10 10
2 2 12
3 6 18
4 10 28

Drop Table CumulativeSum

Select
*,
(Select Sum(SOMENUMT)
From @t S
Where S.id <= M.id)
From @t M