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
Advertisement
Answer
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
ORG_ID | TYPE | VALUE | NEW_VALUE -----: | :--- | ----: | --------: 1 | A | 20 | 50 1 | B | 30 | 30 1 | C | 10 | 10 2 | B | 60 | 60