Summing a column up to a certain row (using GROUP BY and OVER)?

Question

I have a table that lists the duration of different activities. It looks like

id duration  
1  15  
2  30    
3  30  
4  45  
5  30  

...etc

I want to sum these activities like

for (lastActivity=1 to 5)
  SELECT id, SUM(duration) FROM durations
  WHERE id<=lastActivity

to produce an output like

id endtime  
1  15
2  45
3  75  
4  120  
5  150  

where each row sums the duration of the activities up to its position in the list.

It seems an easy task (and possibly is), but I can't figure out how the sql should look like to produce such an output. I have tried using GROUP BY together with the OVER clause but perhaps there's a simpler way of doing this.

Answer 1

you can use running total 

check this post

Running total in sqlserver stackoverflow

Answer 2

SELECT t.id, 
       t.duration, 
       rt.runningTotal 
FROM   mytable t 
       CROSS apply (SELECT Sum(duration) AS runningTotal 
                    FROM   emp 
                    WHERE  id <= t.id) AS rt 
ORDER  BY t.id 

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

Note : To use APPLY, the database compatibility level must be at least 90. This was introduced in sql server 2005.

Answer 3

This will degrade depending on how large your actual table is, but this should do the trick: Some interesting reading around this can be found here

SELECT 1 as id,  15    as num into #test
UNION ALL SELECT 2,  30     
UNION ALL SELECT 3,  30   
UNION ALL SELECT 4,  45   
UNION ALL SELECT 5,  30  

select 
t1.id
,MAX(t1.num) as id_num
,SUM(t2.num) as running_total
from #test t1
LEFT OUTER JOIN #test t2 on t2.id <= t1.id
GROUP BY
t1.id

Answer 4

Try this :

select d2.ID,sum(d1.duration) 
from durations d1,durations d2
where d1.id<=d2.id
group by d2.id