In a table with a primary key of Person_Id and the addresses containing in multiple indexes such as
To get a count of rows for each address, we can use a
GROUP BY and an aggregate...
SELECT t.addr_road_name , t.addr_house_number , t.addr_postal_code , COUNT(DISTINCT t.person_id) AS `cnt_persons` FROM t GROUP BY t.addr_road_name , t.addr_house_number , t.addr_postal_code
If we want to exclude addresses that have a single person, we can add
HAVING COUNT(DISTINCT t.person_id) > 1
For optimal performance with large sets, we want to avoid a "Using filesort" operation by making use of an index that has as leading columns, columns referenced in the
GROUP BY clause, e.g.
... ON t (addr_postal_code, addr_house_number, addr_road_name)