SQL statement HOW to count all relevant rows (A,B) and (B,A) together

I am trying to count all rows where seller and buyer has also an inverted relationship. So the buyer sells to seller. I do not have any clue to group them together.

Table T1|
----------------------
ID    |  SELLER   |   BUYER   |
1     |  U01      |   U02     |
2     |  U03      |   U04     |
3     |  U05      |   U06     |
4     |  U07      |   U08     |
5     |  U09      |   U10     |
6     |  U01      |   U02     |
7     |  U04      |   U03     |
8     |  U02      |   U01     |

Now I would like count the relations between seller and counter.

Result
-------------------------------
Relation       |    Count
U01,U02        |      3
U03,U04        |      2
U05,U06        |      1
U07,U08        |      1
U09,U10        |      1

right now, i am able to count the relation seller to buyer without combining the inverted way with

SELECT SELLER, BUYER, COUNT(*) count
FROM Table T1
Group by SELLER, BUYER

RESULT
-------------------------
SELLER | BUYER | count
U01    | U02   | 2
U02    | U01   | 1
U03    | U04   | 2
U05    | U06   | 1
U07    | U08   | 1
U09    | U10   | 1

But i wold like to have row 1 and 2 together with the count of 3

Any help, advice…

Answer

You can use least() and greatest() if your database supports them (SQL Server is a notable exception):

SELECT 
    LEAST(SELLER, BUYER) id1, 
    GREATEST(SELLER, BUYER) id2, 
    COUNT(*) cnt
FROM Table T1
GROUP BY LEAST(SELLER, BUYER), GREATEST(SELLER, BUYER)

Advertisement

Answer