How to calculate sum on values based on certain rules

[Jairacha]

Hi,

In my table below, I am trying to solve this issue for the value of KEY1. So for all IDs belonging to the same KEY3 (for e.g. IDs:1,2,3 all belong to KEY3: A), the value of KEY1, where KEY2 = X, should be the sum of all KEY1 values belonging to the same KEY3 (for e.g. KEY1s:200,300,500 all belong to KEY3: A). I've added another table below to show what the result should look like.

ID	KEY1	KEY2	KEY3
1	200		A
2	300	X	A
3	500		A
4	800	X	B
5	100		B
6	700		C
7	400		C
8	600	X	C
9	200		D

This is what the result should look like (change in KEY1 values, where KEY2 = X):

ID	KEY1	KEY2	KEY3
1	200		A
2	1000	X	A
3	500		A
4	900	X	B
5	100		B
6	700		C
7	400		C
8	1700	X	C
9	200		D

Thank you! Much appreciated!

[mceleavey · Accepted solution]

Hi @Jairacha ,

nice and simple.

Just sum the KEY1 value grouped by KEY3 then join back to the isolated rows where KEY2=X. This replaces the KEY1 value for these records. Then union back together and sort accordingly.

Hope this helps,

M.