Joining the same table with calculated fields returns "duplicated" rows [closed]-CodePudding

I have to join the same table to sum certain field for each row with the others of the same table.

The problem is that I have row 1 summed with row 2, but the query, when it achieve row 2, gives me another row where it sums row 2 with row 1.

Example:

TABLE

client      quantity

john            2
dave            6
carl            4
david           3

I join the same table to have the sum of quantity of each row with each others but the result gives me

TABLE

client1   client2  quantity1   quantity2     sum
   
john       dave       2            6          8  
dave       john       6            2          8   
...

How can I avoid that query gives me 2 times the same rows sum?

CodePudding user response：

You can do:

select
  a.client as client1, 
  b.client as client2,
  a.quantity as quantity1,
  b.quantity as quantity2 
from my_table a
join my_table b on a.client < b.client

Result:

 client1  client2  quantity1  quantity2 
 -------- -------- ---------- --------- 
 david    john     3          2         
 carl     john     4          2         
 dave     john     6          2         
 carl     dave     4          6         
 carl     david    4          3         
 dave     david    6          3

See running example at db<>fiddle.

The key is in the join predicate. It uses inequality to filter out symmetric pairs.