Accumulate total column

Question

I may need help with a SQL statement. So I have a table called "cont" that looks like this:

cont_id name weight ----------- ---------- ----------- 1 1 10 2 1 20 3 2 40 4 2 15 5 2 20 6 3 15 7 3 40 8 4 60 9 5 10 10 6 5 

Then I summed up the weight column and grouped it by name:

 name wsum ---------- ----------- 2 75 4 60 3 55 1 30 5 10 6 5 

And the result should have an accumulated column and should look like this:

 name wsum acc_wsum ---------- ----------- ------------ 2 75 75 4 60 135 3 55 190 1 30 220 5 10 230 6 5 235 

But I was not able to get the last expression to work.

edit: this expression did it (thanks to Gordon)

 select t.*, (select sum(wsum) from (select name, SUM(weight) wsum from cont group by name) t2 where t2.wsum > t.wsum or (t2.wsum = t.wsum and t2.name <= t.name)) as acc_wsum from (select name, SUM(weight) wsum from cont group by name) t order by wsum desc 

Answer 1

So, the best way to do this is to use the cumulative amount:

 select t.*, sum(wsum) over (order by wsum desc) as acc_wsum from (<your summarized query>) t 

The order by clause makes this cumulative.

If you do not have this feature (in SQL Server 2012 and Oracle), a correlated subquery is an easy way to do this, assuming the summed weights are different values:

 select t.*, (select sum(wsum) from (<your summarized query>) t2 where t2.wsum >= t.wsum) as acc_wsum from (<your summarized query>) t 

This should work in all dialects of SQL. To work with situations when the accumulated weights may have duplicates:

 select t.*, (select sum(wsum) from (<your summarized query>) t2 where t2.wsum > t.wsum or (t2.wsum = t.wsum and t2.name <= t.name) as acc_wsum from (<your summarized query>) t 

Answer 2

try it

 ;WITH CTE AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY wsum) rownum FROM @table1 ) SELECT c1.name, c1.wsum, acc_wsum= (SELECT SUM(c2.wsum) FROM cte c2 WHERE c2.rownum <= c1.rownum) FROM CTE c1; 

or you can join instead of using a subquery

 ;WITH CTE AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY usercount) rownum FROM @table1 ) SELECT c1.name, c1.wsum, acc_wsum= SUM(c2.wsum) FROM CTE c1 INNER JOIN CTE c2 ON c2.rownum <= c1.rownum GROUP BY c1.name, c1.wsum;