Self-referencing Calculated Field

Question

I have a table with data similar to such:

Layer |   LayerPerOccLimit
  1              40             
  2              60               
  3              75
  4              96
  5              102

Basically what I need is a third field that would be a sum of the all of the LayerPerOccLimits that preceed it. So the goal is the Aggregate Column:

Layer | LayerPerOccLimit | *Aggregate*
  1          40                0
  1          80                0
  2          60                120
  3          75                180
  3          25                180 
  4          96                280
  4          10                280
  5          102               386

Edited. Like a running total but has to be grouped by the Layer column.

I have tried various methods such as inserting a primary key and then constructing a case statement based on a combination of less than expressions but I fear that there is conceptual approach that I am just missing entirely.

I am almost less interested in figuring out the actual code than just wrapping my head around the whatever concept I would employ here in a general sense...

You cn view a running total example in SQL Server here: http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver — jle, Feb 28 '14 at 19:00
I see where you are going bu I need to group by the layer column. Apologies. My original data was not accurate enough — StyleCleveland, Feb 28 '14 at 19:09

score 0 · Accepted Answer · answered Feb 28 '14 at 19:14

Try this:

SELECT Layer, LayerPerOccLimit,
  SUM(LayerPerOccLimit) OVER(ORDER BY Layer 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM YourTable

PS: I'm not really sure what you mean as grouped by Layer because your desired result is grouped by both layer and layerPerOccLimit...

Bogdan Sahlean · Answer 2 · 2014-02-28T19:31:52.720

DECLARE @MyTable TABLE (
    LayerID          INT NOT NULL,
    LayerPerOccLimit INT NOT NULL
);
INSERT  @MyTable (LayerID, LayerPerOccLimit)
VALUES  (1, 40), (1, 80), (2, 60), (3, 75), (3, 100);

SELECT  x.LayerID, 
        SUM(SUM(x.LayerPerOccLimit)) OVER(ORDER BY x.LayerID) - SUM(x.LayerPerOccLimit) AS LayerPerOccLimit_RunningTotals
FROM    @MyTable x
GROUP BY x.LayerID;
/*
LayerID LayerPerOccLimit_RunningTotals
------- ------------------------------
1       0
2       120
3       180
*/

SELECT  x.LayerID, x.LayerPerOccLimit, rt.LayerPerOccLimit_RunningTotals
FROM    @MyTable x
INNER JOIN (
    SELECT  x.LayerID, 
        SUM(SUM(x.LayerPerOccLimit)) OVER(ORDER BY x.LayerID) - SUM(x.LayerPerOccLimit) AS LayerPerOccLimit_RunningTotals
    FROM    @MyTable x
    GROUP BY x.LayerID
) rt -- Running totals
ON x.LayerID = rt.LayerID;
/*
LayerID LayerPerOccLimit LayerPerOccLimit_RunningTotals
------- ---------------- ------------------------------
1       40               0
1       80               0
2       60               120
3       75               180
3       100              180
*/

Self-referencing Calculated Field

2 Answers2