Volodymyr Rospotnyuk Volodymyr Rospotnyuk - 8 days ago 5
SQL Question

SQL: Aggregate records within a group taking into account rows order

I have a table of the following structure

| id | a | b |
| 1 | 1 | 1 |
| 2 | 1 | 5 |
| 3 | 2 | 2 |
| 4 | 2 | 3 |
| 5 | 2 | 5 |
| 6 | 1 | 3 |
| 7 | 1 | 7 |
| 8 | 3 | 1 |
| 9 | 2 | 0 |
| 10 | 4 | 8 |


and need to calculate the sum in the column "b" within each group in the column "a" but taking into account the order of rows, i.e. at first we should add
1 + 5
for
a == 1
(
id
s 1 and 2), then we will add
2 + 3 + 5
for
a = 2
(
id
s 3, 4, 5) and so on.

Thus, in the result I need to have the table like this

| a | sum(b) |
| 1 | 6 |
| 2 | 10 |
| 1 | 10 |
| 3 | 1 |
| 2 | 0 |
| 4 | 8 |


Please help to write a SQL query to do this.

Answer
select      a,sum(b)

from       (select   *
                    ,row_number () over (order by id)                as rn
                    ,row_number () over (partition by a order by id) as rnp

            from     t
            ) t

group by    a,rn - rnp    

order by    min(id)  
;          

P.s.
I'm not counting on ID to be a continuous