CODEWITHSUNDEEP
sqloracle-databasedistinct
Giuseppe
Giuseppe

Reputation: 507

SQL Oracle, select distinct unordered couples

My table has two columns which identify two distinct individuals in relation with each other; however, any couple may appear more than once with the individuals switched between the columns (e.g. A sells 100 to B, and B sells 150 to A):

id1  id2  value
 A    B    100
 C    B    110
 B    A    150

I need to obtain a table where each couple appears only once, i.e. I need to find distinct unordered couples, and sum the value from all the corresponding rows:

id1  id2  value
 A    B    250
 C    B    110

How can I get an "index" for the distinct unordered couples? How should I approach this task?

EDIT: Extra issue. Say I also have an attribute for each Id, e.g. the country:

id1  id2  country1 country2 value
 A    B         IT      FR   100
 C    B         US      FR   110
 B    A         FR      IT   150

I obviously can use a join to get them back in the results table, in a second step. However, is there a way to keep them in the first place?

Upvotes: 0

Views: 80

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can use least() and greatest():

select least(id1, id2) as id1, greatest(id1, id2) as id2, sum(value)
from t
group by least(id1, id2), greatest(id1, id2)

Upvotes: 2

Related Questions