Joining the same table with calculated fields returns “duplicated” rows

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

 id       client      quantity

  1       john            2
  2       dave            6
  3       carl            4
  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

idClient1   idClient2  quantity1   quantity2     sum
   
   1           2          2            6          8  
   2           1          6            2          8   
...     

In this case I have the first row with the sum of idClient1 = 1 + idClient2 = 2, so I don’t need the sum of idClient1 = 2 + idClient2 = 1 because the second one is equal to the first one.

How can I avoid that query gives me 2 times the same couple of IdClient1 and idClient2 summed?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0