Accumulate a summarized column

Question

I could need some help with a SQL statement. So I have the table "cont" which looks like that:

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

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

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

And the result should have a accumulated column and should look like that:

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

But I didn't manage to get the last statement working..

edit: this Statement did it (thanks 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

Please show the code of the statement that you couldn't get working. Also, what database are you using? — dan1111, Dec 13 '12 at 14:57
I just found that [this answer](http://stackoverflow.com/a/2563940/1771479) works really well for MySQL. — agold, Oct 26 '15 at 14:18

Gordon Linoff · Accepted Answer · 2012-12-13T15:35:39.683

7

So, the best way to do this is using cumulative sum:

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 don't have that capability (in SQL Server 2012 and Oracle), a correlated subquery is an easy way to do it, assuming the summed weights are distinct 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 where the accumulated weights might 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

edited Dec 13 '12 at 15:35

answered Dec 13 '12 at 15:18

Gordon Linoff

1,242,037
58
646
786

I get the error that order by is invalid in views, inline functions, derived tables.. and if I leave it out it says that object 't' is invalid. – Ulrich Fischer Dec 13 '12 at 15:24
select t.*, (select sum(wsum) from t t2 where t2.name <= t.name) as acc_wsum from ( select name, SUM(weight) wsum from continent group by name ) t – Ulrich Fischer Dec 13 '12 at 15:27
1

@UlrichFischer . . . My apologies. By `t`, I meant your summarized query. In most databases (but not MySQL), you can express this using a CTE and only have to include it once in the query. – Gordon Linoff Dec 13 '12 at 15:36

score 1 · Answer 2 · answered Dec 13 '12 at 14:57

try this

;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 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;

I've never used common table expressions. Could you adjust your code to the table name 'cont'? — Ulrich Fischer, Dec 13 '12 at 15:13

Accumulate a summarized column

2 Answers2

Linked