Form a query to find duplicate zipcodes

Question

Hello, I have a zipcode table. I want to look through this table and find every zipcode that is in it twice. The table does have a primary key set on it(zipcode_rid). How would I do this?

Also, I am aware that there are official multi-state/city/county zipcodes. That is what I'm wishing to find.

My table structure is like so:

• zipcode_rid (int, unique, primary key)
• zipcode (int)
• city (varchar(50))
• state (varchar(50))
• county (varchar(50))

The database software I am using is Sql Server 2008 Express

Answer 1

To get a list of zipcodes with duplicates, use:

  SELECT t.zipcode
    FROM TABLE t
GROUP BY t.zipcode
  HAVING COUNT(t.zipcode) > 1

Answer 2

Here is a query that gives you all the zip codes that occur in the table twice. You can change the "= 2" portion to be "> 1" or whathaveyou. I've included the COUNT() in there because if you use ">1" you can see exactly how many times it appears.

SELECT
    COUNT(zipcode_rid) AS No_of_Zipcodes
    ,zipcode
FROM
    Zipcodes
GROUP BY
    zipcode
HAVING
    COUNT(zipcode_rid) = 2