CODEWITHSUNDEEP
postgresqlgroup-byunion
bublitz
bublitz

Reputation: 920

Postgresql - Group by set of columns

I have a question regarding group by in PostgreSQL.

I am currently looking at the following table:

---------------
t1     t2     n
---------------
a      b      1
b      a      2
c      a      4

and want to get the following result:

---------------
t1     t2     n
---------------
a      b      3
c      a      4

In words, I want to group by the set of columns t1 and t2 and sum over the column n within that group. I tried different approaches like the union with t2,t1, however, I am not able to get rid of the "duplicate" entry "b a 3" in this case.

Thank you for your help.

Upvotes: 1

Views: 62

Answers (1)

user330315
user330315

Reputation: 

select least(t1, t2) as t1, greatest(t1, t2) as t2, sum(n)
from the_table
group by least(t1, t2), greatest(t1, t2)

This however returns a,c,4 not c,a,4 but as you seem to want to treat a,c the same as c,a this shouldn't make a difference.

Upvotes: 1

Related Questions