How to Get Running Sum of a Column in SQL Server

Adding a running total column in SQL Server

try SUM() OVER

SELECT  
      item, 
      week, 
      amount,
      SUM(amount) over (partition by item order by Week) as Total
FROM yourTable

How to find running sum over two columns in SQL

You are quite close, except you are missing the partition by:

select month, region,
       sum(value) over (partition by region order by month rows unbounded preceding) as cumulative_sum
from table

How to get cumulative sum

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

Output

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

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!

SQL: Select the Running Total of Column C for pairwise combinations of two other columns A and B

You want a partition by clause:

SELECT t.*,
       SUM(amount) OVER (PARTITION BY label 
                         ORDER BY amount
                        ) AS running_total
FROM test t
ORDER BY bucket, amount;

Although ORDER BY amount works for your data, I think you might really want the bucket as well:

SELECT t.*,
       SUM(amount) OVER (PARTITION BY label 
                         ORDER BY bucket, amount
                        ) AS running_total
FROM test t
ORDER BY bucket, amount;

Here is a db<>fiddle. Note that this uses MySQL 8, because your create/insert code is compatible with MySQL.

Calculate Running total over calculated column SQL Server

If using SQL Server 2012+, using SUM() with the OVER() Clause would allow you to achieve a running total.

For example,

SELECT TimeElapsed,
       CurrentValue,
       CummuSum = SUM(CurrentValue) OVER (ORDER BY TimeElapsed)
FROM 
(
    SELECT TimeElapsed = DATEDIFF(HOUR, Date1, Date2),
           CurrentValue = CAST(COUNT(Date2) AS FLOAT) / (SELECT COUNT(Col3) FROM myTable)
    FROM myTable
    GROUP BY DATEDIFF(HOUR, Date1, Date2)
) T
ORDER BY TimeElapsed;

An alternative way of achieving this (which would work in SQL Server 2008), is using a CTE with a subquery in the select statement.

For example,

WITH CTE AS
(
    SELECT TimeElapsed = DATEDIFF(HOUR, Date1, Date2),
           CurrentValue = CAST(COUNT(Date2) AS FLOAT) / (SELECT COUNT(Col3) FROM myTable)
    FROM myTable
    GROUP BY DATEDIFF(HOUR, Date1, Date2)
) 
SELECT TimeElapsed,
       CurrentValue,
       CummuSum = (SELECT SUM(CurrentValue) FROM CTE WHERE TimeElapsed <= C.TimeElapsed)
FROM CTE C;

Cumulative sum of a column

declare @t table(Country varchar(5), Level int, Num_of_Duplicates int)
insert into @t(Country, Level, Num_of_Duplicates)
values
('US', 9, 6),
('US', 8, 24),
('US', 7, 12),
('US', 6, 20),
('US', 5, 39),
('US', 4, 81),
('US', 3, 80),
('US', 2, 130/*-92*/),
('US', 1, 178),
('US', 0, 430);

select *, sum(Num_of_Duplicates) over(partition by country order by Level desc),
(sum(Num_of_Duplicates) over(partition by country order by Level desc)-Num_of_Duplicates) / 300 as flag,--any row which starts before 300 will have flag=0
--or
case when sum(Num_of_Duplicates) over(partition by country order by Level desc)-Num_of_Duplicates < 300 then 1 else 0 end as startsbefore300
from @t;

select *
from
   (
    select *, sum(Num_of_Duplicates) over(partition by country order by Level desc) as Pool
    from @t
) as t
where Pool - Num_of_Duplicates < 300 ;

Calculate a Running Total in SQL Server

Update, if you are running SQL Server 2012 see: https://stackoverflow.com/a/10309947

The problem is that the SQL Server implementation of the Over clause is somewhat limited.

Oracle (and ANSI-SQL) allow you to do things like:

 SELECT somedate, somevalue,
  SUM(somevalue) OVER(ORDER BY somedate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM Table

SQL Server gives you no clean solution to this problem. My gut is telling me that this is one of those rare cases where a cursor is the fastest, though I will have to do some benchmarking on big results.

The update trick is handy but I feel its fairly fragile. It seems that if you are updating a full table then it will proceed in the order of the primary key. So if you set your date as a primary key ascending you will probably be safe. But you are relying on an undocumented SQL Server implementation detail (also if the query ends up being performed by two procs I wonder what will happen, see: MAXDOP):

Full working sample:

drop table #t 
create table #t ( ord int primary key, total int, running_total int)

insert #t(ord,total)  values (2,20)
-- notice the malicious re-ordering 
insert #t(ord,total) values (1,10)
insert #t(ord,total)  values (3,10)
insert #t(ord,total)  values (4,1)

declare @total int 
set @total = 0
update #t set running_total = @total, @total = @total + total 

select * from #t
order by ord 

ord         total       running_total
----------- ----------- -------------
1           10          10
2           20          30
3           10          40
4           1           41

You asked for a benchmark this is the lowdown.

The fastest SAFE way of doing this would be the Cursor, it is an order of magnitude faster than the correlated sub-query of cross-join.

The absolute fastest way is the UPDATE trick. My only concern with it is that I am not certain that under all circumstances the update will proceed in a linear way. There is nothing in the query that explicitly says so.

Bottom line, for production code I would go with the cursor.

Test data:

create table #t ( ord int primary key, total int, running_total int)

set nocount on 
declare @i int
set @i = 0 
begin tran
while @i < 10000
begin
   insert #t (ord, total) values (@i,  rand() * 100) 
    set @i = @i +1
end
commit

Test 1:

SELECT ord,total, 
    (SELECT SUM(total) 
        FROM #t b 
        WHERE b.ord <= a.ord) AS b 
FROM #t a

-- CPU 11731, Reads 154934, Duration 11135 

Test 2:

SELECT a.ord, a.total, SUM(b.total) AS RunningTotal 
FROM #t a CROSS JOIN #t b 
WHERE (b.ord <= a.ord) 
GROUP BY a.ord,a.total 
ORDER BY a.ord

-- CPU 16053, Reads 154935, Duration 4647

Test 3:

DECLARE @TotalTable table(ord int primary key, total int, running_total int)

DECLARE forward_cursor CURSOR FAST_FORWARD 
FOR 
SELECT ord, total
FROM #t 
ORDER BY ord

OPEN forward_cursor 

DECLARE @running_total int, 
    @ord int, 
    @total int
SET @running_total = 0

FETCH NEXT FROM forward_cursor INTO @ord, @total 
WHILE (@@FETCH_STATUS = 0)
BEGIN
     SET @running_total = @running_total + @total
     INSERT @TotalTable VALUES(@ord, @total, @running_total)
     FETCH NEXT FROM forward_cursor INTO @ord, @total 
END

CLOSE forward_cursor
DEALLOCATE forward_cursor

SELECT * FROM @TotalTable

-- CPU 359, Reads 30392, Duration 496

Test 4:

declare @total int 
set @total = 0
update #t set running_total = @total, @total = @total + total 

select * from #t

-- CPU 0, Reads 58, Duration 139

---

Related Topics