Add value of type B to type A of same org_id

Question

I have a table like this

ORG_ID  TYPE  VALUE
1        A     20
1        B     30
1        C     10
2        B     60

Now I want to select values from this table for each org_id, type but for type A i want to add value of type B of same org_id to type A. My query should return this

VALUE
50
30
10
60

In this case, when ORG_ID is 1 and TYPE is 'A', I have to add that organization's type B value, which is 30. But I do not need to add the last value because that belongs to another organization

Answer 1

You can use window functions:

select t.*,
    case when type = 'A'
        then value + max(case when type = 'B' then value else 0 end) over(partition by org_id) 
        else value 
    end as new_value
from mytable t

Demo on DB Fiddle:

ORG_ID | TYPE | VALUE | NEW_VALUE
-----: | :--- | ----: | --------:
     1 | A    |    20 |        50
     1 | B    |    30 |        30
     1 | C    |    10 |        10
     2 | B    |    60 |        60