I would like to sum values in my table, except the ones that are absolute (field absolute, value = 1). If that’s the case, the summing should reset. Example:

Date Value Absolute 1-1-2020 4 0 1-2-2020 7 1 1-3-2020 3 0

Regular SUM() would return (4+7+3=) 14. But in this example it should reset at value 7, which makes a sum of (7+3=) 10.

How can I make this work?

## Advertisement

## Answer

You seem to want a window sum that resets everytime `absolute`

is `1`

. If so, you can do:

select t.*, sum(value) over(partition by grp order by date) sum_value from ( select t.*, sum(absolute) over(order by date) grp from mytable t ) t

The subquery uses a window sum of `absolute`

to define the groups, then the outer query performs the sums `value`

over each group.