SQL Server How to select SUM of previous record in group

hof1towb 于 2023-11-16 发布在其他

关注(0)|答案(2)|浏览(84)

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 |

I've tried:

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

but it also includes current row

sql-server

来源：https://stackoverflow.com/questions/77408499/how-to-select-sum-of-previous-record-in-group

2条答案

按热度按时间

hmtdttj41#

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.

Demo here

赞(0）回复(0）举报 2023-11-16

kkih6yb82#

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

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server How to select SUM of previous record in group

Question

I've tried:

2条答案

相关问题

热门标签

最新问答