Grouping by pairs of values that are interchangeably used-CodePudding

So, basically I want to count the number of unique set of ID pairs. Here is the interactive demo.

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=aa5e732b8e980358a84fa255e83114b5

Data:

SENDER	RECEIVER	TRANSACTION ID
1234	5678	1
1234	5678	4
5678	1234	5
5678	1234	9
5678	9876	12
9876	5678	7

Desired output:

SENDER	RECEIVER	TRANSACTION COUNT
1234	5678	4
5678	9876	2

Or:

SENDER	RECEIVER	TRANSACTION COUNT
5678	1234	4
9876	5678	2

CodePudding user response：

Use least and greatest to get the lower value first:

select least(sender, receiver), greatest (sender, receiver), count(*)
from tablename
group by least(sender, receiver), greatest (sender, receiver)

CodePudding user response：

Use LEAST and GREATEST:

SELECT LEAST(sender, receiver) AS sender,
       GREATEST(sender, receiver) AS receiver,
       COUNT(*) AS transaction_count
FROM   table_name
GROUP BY
       LEAST(sender, receiver),
       GREATEST(sender, receiver)

Which, for your sample data:

CREATE TABLE table_name (SENDER, RECEIVER, TRANSACTION_ID) AS
SELECT 1234, 5678,  1 FROM DUAL UNION ALL
SELECT 1234, 5678,  4 FROM DUAL UNION ALL
SELECT 5678, 1234,  5 FROM DUAL UNION ALL
SELECT 5678, 1234,  9 FROM DUAL UNION ALL
SELECT 5678, 9876, 12 FROM DUAL UNION ALL
SELECT 9876, 5678,  7 FROM DUAL;

Outputs:

SENDER RECEIVER TRANSACTION_COUNT

5678 9876 2

1234 5678 4

SENDER	RECEIVER	TRANSACTION_COUNT
5678	9876	2
1234	5678	4

db<>fiddle here