I have a following table.
Name count1 Name2 count2
A 1 B 2
B 2 c 4
A 5 C 7
Name has count1 values and Name2 has count2 values.
I need the following result
a -> 6 col[0][0] + c[2][0]
b-> 4 col[0][3] + c[1][1]
c -> 11 col[1][3] + col[2][3]
Explanation: B comes in both name and name1 so we need to add both count1 and count2 for the B
You need to unpivot the data and then aggregate. Here is a simple method:
select name, sum(cnt)
from ((select name1 as name, count1 as cnt from t) union all
(select name2, count2 from t)
) t
group by name;
More recent versions of Postgres support lateral joins. These can be more efficient when you have a large amount of data, but union all also works fine.
EDIT:
A lateral join is very similar:
select v.name, sum(v.cnt)
from t, lateral join
(values (t.name1, t.count1), (t.name2, t.count2)) v(name, cnt)
group by v.name;
