Skip to content
Advertisement

SQL cumulative sum with mutilple conditions

I have a input table as this:

I would like to calculate the cumulative sum of the flag for each, but the calculation should be refreshed if the flag is equal to 0 once again.

So, the output table should be:

So, any suggestion to solve this issue? I tried with this:

But i have no idea how can I “refresh” the cumulative calculation, any idea?

Thanks in advance!

Advertisement

Answer

You need to assign a grouping. The grouping is the number of 0s before each row. Then use that for partitioning:

Note: The window frame specification (rows between . . .) is not needed assuming that the dates are unique (as in your sample data). So, I just left it out.

Also, you can simplify the calculation in the subquery to:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement