Sum by Groups Before and after appear Number in column SQL-CodePudding

I have a group of records by id ordered by date ('Date'), which I want to sum up the amounts or, in 2 groups, 1 before any number appears ('Condition'), and another group after the first number,It doesn't matter if a 0 appears after a number, add before any number appears, and add after.

CodePudding user response：

You can use conditional aggregation. For example:

select
  id,
  sum(case when s = 0 then amount else 0 end) as amount_before,
  sum(case when s <> 0 then amount else 0 end) as amount_after  
from (
  select t.*,
    sum(abs(condition)) over(partition by id order by date) as s
  from t
) x
group by id