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.
x
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…
Advertisement
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)