PostgreSQL COUNT DISTINCT on one column while checking duplicates of another column

Question

I have a query that results in such a table:

guardian_id | child_id | guardian_name | relation | child_name |
------------|----------|---------------|----------|------------|
    1       |   1      | John Doe      | father   | Doe Son    |
    2       |   1      | Jane Doe      | mother   | Doe Son    |
    3       |   2      | Peter Pan     | father   | Pan Dghter |
    4       |   2      | Pet Pan       | mother   | Pan Dghter |
    1       |   3      | John Doe      | father   | Doe Dghter |
    2       |   3      | Jane Doe      | mother   | Doe Dghter |

So from these results, I need to count the families. That is, distinct children with the same guardians. From the results above, There are 3 children but 2 families. How can I achieve this?

If I do:

SELECT COUNT(DISTINCT child_id) as families FROM (
  //larger query
)a

I'll get 3 which is not correct. Alternatively, how can I incorporate a WHERE clause that checks DISTINCT guardian_id's? Any other approaches?

Also note that there are instances where a child may have one guardian only.

Are you really still using Postgres 9.1? That has been out of support for [over two years now)(postgresql.org/support/versioning). You should plan your upgrade as soon as possible. — user330315
– user330315, Commented Jan 9, 2019 at 9:01
Yes, the system is heavily reliant on postgres 9.1 (multiple applications using the same RDBMS (postgres 9.1)- as for the upgrade, other more senior members of the team have the say on that — Clint_A
– Clint_A, Commented Jan 9, 2019 at 9:11

HiteshRawal · Accepted Answer · 2019-01-09 09:56:59Z

1

To get the distinct family you can try the following approach.

select distinct array_agg(distinct guardian_id) 
from family
group by child_id;

The above query will return the list of unique families. eg.

{1,2}
{3,4}

Now you can apply the count on top of it.

answered Jan 9, 2019 at 9:56

HiteshRawal

4552 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL COUNT DISTINCT on one column while checking duplicates of another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related