Account | date | Amount |
---|---|---|
X1 | 20230501 | 10 |
X2 | 20230501 | 11 |
X3 | 20230501 | 12 |
X1 | 20230502 | 20 |
X3 | 20230502 | 21 |
X1 | 20230503 | 30 |
X4 | 20230503 | 10 |
I want to retrieve a running total of "Amount" using windows function to get the following output:
Account | date | Running Total |
---|---|---|
X1 | 20230501 | 10 |
X2 | 20230501 | 11 |
X3 | 20230501 | 12 |
X1 | 20230502 | 30 |
X2 | 20230502 | 11 |
X3 | 20230502 | 33 |
X1 | 20230503 | 60 |
X2 | 20230503 | 11 |
X3 | 20230503 | 33 |
X4 | 20230503 | 10 |
I've tried the following SQL approach:
SELECT Account,
Date,
SUM(Amount) OVER (PARTITION BY Account ORDER BY Date) AS RunningTotal
FROM tab1;
But it does not provide data as above. How do I get output as above to show data for accounts which are no longer as of latest date?
2条答案
按热度按时间bqjvbblv1#
One possible way of addressing this problem is to:
Output
Check the demo here .
The last
ORDER BY
clause is not necessary: it's just for visualization purposes. On a side note, if you do mind about having the null values, you can filter them out in a subquery.xfb7svmp2#
Another example, similar to @lemon answer. Added checking for first date for Account. Without full calendar.
Result