SQL SUM() Window Function

Question

I have data that is grouped by a term limit and I am trying to get a running total of all the data using a window function. The problem is that each new record is already summed and I have already tried using EXCLUDE TIES but problem is that it will only work for the current rows.

EX: SUM(Value1) OVER (ORDER BY TERM ROWS UNBOUNDED PRECEDING EXCLUDE TIES)

Value1	Term	Expected Results
0.5	1	0.5
0.75	1	0.75
1	1	1
0.25	2	1.25
0.5	2	1.5
1	2	2
0.13	3	2.13
0.65	3	2.65
0.75	3	2.75

The question I am asking is, is there a way to group each term for previous terms with the latest values and then exclude ties on just the current row to get a correct 'running total'? The highest 'Value1' will ever go is to 1 if that helps at all.

Answer 1

Maybe something like this:

WITH xrows AS (
        SELECT x.*
             , value1 - COALESCE(LAG(value1) OVER (PARTITION BY term ORDER BY value1), 0) AS diff
          FROM test AS x
     )
SELECT x.*
     , SUM(diff) OVER (ORDER BY term, value1) AS rsum
  FROM xrows AS x
 ORDER BY term, value1
;

and just a derived table, without a WITH clause:

SELECT x.*
     , SUM(diff) OVER (ORDER BY term, value1) AS rsum
  FROM (SELECT x.*
             , value1 - COALESCE(LAG(value1) OVER (PARTITION BY term ORDER BY value1), 0) AS diff
          FROM test AS x
       ) AS x
 ORDER BY term, value1
;

The result:

+--------+------+----------+------+------+
| value1 | term | expected | diff | rsum |
+--------+------+----------+------+------+
|   0.50 |    1 |     0.50 | 0.50 | 0.50 |
|   0.75 |    1 |     0.75 | 0.25 | 0.75 |
|   1.00 |    1 |     1.00 | 0.25 | 1.00 |
|   0.25 |    2 |     1.25 | 0.25 | 1.25 |
|   0.50 |    2 |     1.50 | 0.25 | 1.50 |
|   1.00 |    2 |     2.00 | 0.50 | 2.00 |
|   0.13 |    3 |     2.13 | 0.13 | 2.13 |
|   0.65 |    3 |     2.65 | 0.52 | 2.65 |
|   0.75 |    3 |     2.75 | 0.10 | 2.75 |
+--------+------+----------+------+------+

The setup:

CREATE TABLE test (value1 decimal(8,2), term int, expected decimal(8,2));

INSERT INTO test VALUES
  (0.5 , 1, 0.5 )
, (0.75, 1, 0.75)
, (1   , 1, 1   )
, (0.25, 2, 1.25)
, (0.5 , 2, 1.5 )
, (1   , 2, 2   )
, (0.13, 3, 2.13)
, (0.65, 3, 2.65)
, (0.75, 3, 2.75)
;