Postgres duplicate phone numbers where name is different

Question

Using a simple table consisting of firstname, lastname, and phone (all text fields), how can I find how many unique phone numbers are used by more than one person?

I know how to find how many duplicate phone numbers there are, but I only want a count of how many when the phone number is a duplicate where the firstname + lastname + phone is different.

I suspect it would be something similar to the following that shows how many complete duplicates there are:

SELECT firstname, lastname, phone
FROM people
GROUP BY lastname, firstname, phone
HAVING count(*) >1

Answer 1

Use two levels of aggregation:

select count(*)
from (
    select 1
    from mytable
    group by phone
    having count(*) > 1
) t

This assumes that the same phone number is not affected twice to the same person.

Another option is window functions (which also properly handle the above case):

select count(*) filter(where rn = 2)
from (
    select dense_rank() over(partition by phone order by firstname, lastname) rn
    from mytable
) t