How to group adjacent row and sum the data in SQL

Question

I would like to sum the Value and group the adjacent row in SQL as shown below. May I know how to do that?

My code now:

Select ID, Value from Table_1 

Further question

how about this?

Answer 1

This is a typical gaps and island problem.

As a starter: keep in mind that SQL tables represents unordered set of rows. So for your question to be solved, you need a column that defines the ordering of rows across the table - I assumed ordering_id.

Here is an approach that uses the difference between row_numbers() to build the groups of adjacent rows having the same id:

select 
    id,
    sum(value) value
from (
    select 
        t.*,
        row_number() over(order by ordering_id) rn1
        row_number() over(partition by id order by ordering_id) rn2
    from mytable t
) t
group by id, rn1 - rn2

If you want this on a per user basis:

select 
    user,
    id,
    sum(value) value
from (
    select 
        t.*,
        row_number() over(partition by user order by ordering_id) rn1
        row_number() over(partition by user, id order by ordering_id) rn2
    from mytable t
) t
group by user, id, rn1 - rn2