# 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?

Contents

### 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. 