Peter Peter - 3 years ago 266
MySQL Question

Count amount of people in addresses SQL

In a table with a primary key of Person_Id and the addresses containing in multiple indexes such as


As some of the addresses are duplicated is it possible to create a unique identifier for each address from the separate indexes using SQL queries?

Then with the unique identifier, find how many People via Person_Ids are at each address?

Answer Source

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
     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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download