How to select SUM of previous record in group-CodePudding

Question

What is the best or most expressive way to write SQL Query that selects

current row and sum of previous row withing a group

For example, given a table with columns Id, Group, Amount, how to calculate PreviousAmount

Result:

Id	Group	Amount	PreviousAmount*
1	1	2	NULL
2	1	2	2
3	1	2	4
4	2	2	NULL

SELECT
    *,
    SUM(Amount) OVER (PARTITION BY Group ORDER BY ID RANGE UNBOUNDED PRECEDING) as Prev
FROM Table

but it also includes current row

CodePudding user response：

Here how to do it :

SELECT
    *,
    SUM(Amount) OVER (PARTITION BY [Group] ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as Prev
FROM mytable

ROWS UNBOUNDED PRECEDING means the frame's lower bound is simply infinite.

1 PRECEDING represent the row before the current row, it is the higher bound within the partition.

CodePudding user response：

The simplest way is to use default range and to substract the current row amount

select *, SUM(Amount) OVER (PARTITION BY [Group] ORDER BY ID) - Amount as Prev
from tbl