CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Peter
Peter

Reputation: 17

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 

Road_Name, 
House_Number, 
Postcode

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?

Upvotes: 0

Views: 508

Answers (1)

spencer7593
spencer7593

Reputation: 108490

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)

Upvotes: 3

Related Questions