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