How can I count the number of unique pairs in a table that has a recursive relationship?

select count(*) from table
where personID in (select coupleID from table)
and coupleID in (select personid from table)
and coupleID > personID

hope it helps!


Logic

  • use inner join + join table's PersonId = your table's CoupleId and join table's CoupleId = your table's PersonId
  • because inner join will get double rows so it have to count(1) / 2 get the count.

Schema (Oracle v11g)

CREATE TABLE T
    ("PersonId" int, "CoupleId" int)
INSERT ALL 
    INTO T ("PersonId", "CoupleId")
         VALUES (1, 2)
    INTO T ("PersonId", "CoupleId")
         VALUES (2, 1)
    INTO T ("PersonId", "CoupleId")
         VALUES (3, null)
    INTO T ("PersonId", "CoupleId")
         VALUES (4, 5)
    INTO T ("PersonId", "CoupleId")
         VALUES (5, 4)
SELECT * FROM dual

Not Allow PersonId and CoupleId Duplicate Version

select count(1) / 2 cnt  from T T1
inner join T  T2 on T1."PersonId" = T2."CoupleId" and T1."CoupleId" = T2."PersonId"

cnt
---
2

Allow PersonId and CoupleId Duplicate Version

with cte as (
    select distinct * from T
)
select count(1) / 2 cnt  from CTE T1
inner join CTE T2 on T1."PersonId" = T2."CoupleId" and T1."CoupleId" = T2."PersonId"

cnt
---
2

View on db<>fiddle


You can apply least() and greatest() functions to personid and coupleid
and group by them and finally count the number of rows:

select count(*) counter from (
  select least(personId, coupleId), greatest(personId, coupleId)  
  from people_table
  where personId is not null and coupleId is not null
  group by least(personId, coupleId), greatest(personId, coupleId)
)

See the demo

Tags:

Sql

Oracle