Sum column up to the current row in SQL?

Question

I'm trying to sum a column up to the current row (in SQL Server). How do I do this?

select t1.CounterTime,
       t1.StartTime,
       t1.EndTime,
       isNull(t1.value, 0) as value1,

       -- How do I make Total1 the sum of t1.value over all previous rows?
       sum( isNull(t1.value, 0) ) over (partition by t1.CounterTime order by t1.CounterTime) as Total1

from   SomeTable t1
order by t1.CounterTime

But I got the partition by wrong...

╔═══╦═════════════════════════╦═════════════════════════╦═════════════════════════╦════════╦════════╗
║   ║      CounterTime        ║        StartTime        ║         EndTime         ║ value1 ║ Total1 ║
╠═══╬═════════════════════════╬═════════════════════════╬═════════════════════════╬════════╬════════╣
║ 1 ║ 2015-03-17 12:00:00.000 ║ 2015-03-17 00:00:00.000 ║ 2015-03-18 00:00:00.000 ║     0  ║     0  ║
║ 2 ║ 2015-03-18 12:00:00.000 ║ 2015-03-18 00:00:00.000 ║ 2015-03-19 00:00:00.000 ║     0  ║     0  ║
║ 3 ║ 2015-03-19 12:00:00.000 ║ 2015-03-19 00:00:00.000 ║ 2015-03-20 00:00:00.000 ║   422  ║   422  ║
║ 4 ║ 2015-03-20 12:00:00.000 ║ 2015-03-20 00:00:00.000 ║ 2015-03-21 00:00:00.000 ║   989  ║   989  ║
║ 5 ║ 2015-03-21 12:00:00.000 ║ 2015-03-21 00:00:00.000 ║ 2015-03-22 00:00:00.000 ║  1162  ║  1162  ║
║ 6 ║ 2015-03-22 12:00:00.000 ║ 2015-03-22 00:00:00.000 ║ 2015-03-23 00:00:00.000 ║   524  ║   524  ║
╚═══╩═════════════════════════╩═════════════════════════╩═════════════════════════╩════════╩════════╝

Should be:

╔════════╗
║ Total1 ║
╠════════╣
║     0  ║
║     0  ║
║   422  ║
║  1411  ║
║  2573  ║
║  3097  ║
╚════════╝

This might be very related, if not a dup: http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver — dotNET, Apr 17 '15 at 09:44
If you want sum of all previous rows, just remove `partition by t1.CounterTime ` and keep `order by t1.CounterTime` — ughai, Apr 17 '15 at 09:45

score 5 · Accepted Answer · answered Apr 17 '15 at 09:45

Try this
Remove the "partition by" use only "order by"

select t1.CounterTime,
           t1.StartTime,
           t1.EndTime,
           isNull(t1.value, 0) as value1,

           -- How do I make Total1 the sum of t1.value over all previous rows?
           sum( isNull(t1.value, 0) ) over (order by t1.CounterTime) as Total1

    from   SomeTable t1
    order by t1.CounterTime

score 1 · Answer 2 · answered Apr 17 '15 at 10:00

here it is an sample example to calculate running totals

declare @t table (id int,val int)
insert into @t(id,val)values (1,0),(2,0),(3,411),(4,989),(5,1162),(6,524)

SELECT ID, val, RunningTotal = val + COALESCE(
(
  SELECT SUM(val)
    FROM @t AS i
    WHERE i.ID < o.ID), 0
)
FROM @t AS o
ORDER BY ID;

IN Sql 2012

SELECT ID, val, 
  RunningTotal = SUM(val) OVER (ORDER BY TID ROWS UNBOUNDED PRECEDING)
FROM @t
ORDER BY TID;

the same code can be used into your query my guess countertime can be used Id's then it will work as per result

select t1.CounterTime,
           t1.StartTime,
           t1.EndTime,
           isNull(t1.value, 0) as value1,
           RunningTotal = t1.value + COALESCE(
(
  SELECT SUM(value)
    FROM SomeTable AS i
    WHERE i.CounterTime < o.CounterTime), 0
)

    from   SomeTable t1
    order by t1.CounterTime

Sum column up to the current row in SQL?

2 Answers2

Linked