Grouping by pairs of values that are interchangeably used

user_9582381 0
Viewed 42

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
oracle sql
2 Answers

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)
user_37060160

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

db<>fiddle here

user_15092640
Related