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.