Junaid - 9 months ago 46

SQL Question

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

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.