CODEWITHSUNDEEP
sqlpostgresql
Luke
Luke

Reputation: 553

I need to select from a table in a situation where one column cannot have duplicates

Here is the raw data:

leasetenantssn | houseid

  641078001 |     100
  699061306 |     100
  734296097 |     200
  788930793 |     200
  699061306 |     200
  641078001 |     300
  674926108 |     300
  709106079 |     400
  641078001 |     400
  664067824 |     400
  709106079 |     500
  709106079 |     600
  709106079 |     600
  699061306 |     800
  641078001 |    1000
  699061306 |    1000
  690260126 |    1000
  690260126 |    1100

I am trying to select all the SSNs that more than 3 unique houseids attached to them.

My first attempt was simple:

Select leasetenantSSN from tenants group by leasetenantSSN having count(*) > 3;

And this returned 709106079, 641078001, 699061306. But the problem is, 709106079 only has two unique houseids, even though they have 3 houseids total.

They should not have been returned.

This was my next attempt: 

 Select distinct on (houseid) leasetenantSSN from tenants group by  houseid  having count(*) > 3;

But this throws an error, because it wants leasetenantSSN to be in the group by, but I can't do that because then it won't return anything.

So I don't know what to do now.

Upvotes: 0

Views: 39

Answers (1)

Gab
Gab

Reputation: 3520

I am trying to select all the SSNs that more than 3 unique houseids attached to them.

Here you go:

SELECT t.leasetenantSSN
FROM tenants t
WHERE (
        SELECT count(DISTINCT houseid)
        FROM tenants
        WHERE leasetenantSSN = t.leasetenantSSN
     ) > 3
GROUP BY t.leasetenantSSN
;

->

leasetenantssn 
----------------
     641078001
     699061306
(2 rows)

Please consider encrypting social security numbers, they are very sensitive for the people they belong to.

Upvotes: 1

Related Questions