CODEWITHSUNDEEP
sqlpostgresql
Lakhan Jain
Lakhan Jain

Reputation: 161

Need help in creating PostgreSQL query

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

Upvotes: 2

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 1

Related Questions