This is a really fun question (asked for SQL Server) and I wanted to try it out to see how it was done in PostgreSQL. Let's see if anyone else can do it better. Taking this data,

CREATE TABLE foo AS SELECT pkid::int, numvalue::int, groupid::int FROM ( VALUES ( 1, -1 , 1 ), ( 2, -2 , 1 ), ( 3, 5 , 1 ), ( 4, -7 , 1 ), ( 5, 1 , 2 ) ) AS t(pkid, numvalue, groupid);

We're trying to generate this:

PKID RollingSum GroupID ----------------------------- ## Explanation: 1 0 1 ## 0 - 1 < 0 => 0 2 0 1 ## 0 - 2 < 0 => 0 3 5 1 ## 0 + 5 > 0 => 5 4 0 1 ## 5 - 7 < 0 => 0

The problem is described as,

When adding a negative number will cause the sum to be negative, the limit will be activated to set the result as zero. Subsequent addition should be based on this adjusted value, instead of the original rolling sum. The expected result should be achieved using addition. If the fourth number changes from -7 to -3, the fourth result should be 2 instead of 0 If a single sum can be provided rather than a few rolling numbers, it would also be acceptable. I can use stored procedures to implement a non-negative addition, but that would be too low-level. The real life problem for this is that we record order placed as positive amount and cancelled as negative. Due to connectivity issues customers may click the cancel button more than once, which will result in multiple negative values being recorded. When calculating our revenue, "zero" need to be a boundary for sales.

Their solutions are all using recursion.