Junaid Junaid - 9 months ago 46
SQL Question

Count Distinct Composite Columns

I am trying to count distinct composite columns using sql. Below is my query

SELECT distinct_val.LeaseTenantSSN, count(distinct_val.LeaseTenantSSN) = 3
FROM (select distinct houseid, LeaseTenantSSN from tenants) AS distinct_val
group by distinct_val.LeaseTenantSSN;


Result

Result

What I want to get is

SELECT distinct_val.LeaseTenantSSN
FROM (select distinct houseid, LeaseTenantSSN from tenants) AS distinct_val
where count(distinct_val.LeaseTenantSSN) = 3
group by distinct_val.LeaseTenantSSN;


But it gives error on where statement. I am using Postgresql

Answer Source

Use HAVING:

SELECT distinct_val.LeaseTenantSSN
FROM (select distinct houseid, LeaseTenantSSN from tenants) AS distinct_val 
group by distinct_val.LeaseTenantSSN
having count(distinct_val.LeaseTenantSSN) = 3

This may fix your error, but I suspect that you really want something like the following:

SELECT LeaseTenantSSN
FROM tenants
GROUP BY LeaseTenantSSN
HAVING COUNT(DISTINCT houseid) = 3

This would return every tenant SSN which had exactly three distinct houses associated with him.