SQL query to count and list the different counties per zip code

Question

I have a sql server 2005 table called ZipCode, which has all the US ZIPs in it. For each entry, it lists the zip, county, city, state, and several other details.

Some zipcodes span multiple cities and some even span multiple counties. As a result, some zipcodes appear many times in the table.

I am trying to query the table to see which zipcodes go across multiple counties.

This is what I have so far:

select
    zipcode,
    count(zipcode) as total,
    county,
    state
from
    zipcode
group by
    zipcode,
    county,
    state
order by
    zipcode

Of 19248 records in the result set, here are the first several records returned:

zipcode total county state
00501   2 SUFFOLK NY
00544   2 SUFFOLK NY
00801   3 SAINT THOMAS VI
00802   3 SAINT THOMAS VI
00803   3 SAINT THOMAS VI
00804   3 SAINT THOMAS VI
00805   1 SAINT THOMAS VI
00820   2 SAINT CROIX VI
00821   1 SAINT CROIX VI
00822   1 SAINT CROIX VI
00823   2 SAINT CROIX VI
00824   2 SAINT CROIX VI

In this particular example, each zip with a total of two or more happens to be in the table more than once, and it's because the "cityaliasname" (not shown) or some other column differs. But I just want to know which zips are in there more than once because the county column differs.

I searched before posting this and I found many questions about counting records but I could not figure out how to apply them to my problem. Please forgive me if there is already a question whose answer applies to this question.

Answer 1

SELECT zipcode,count(county) FROM table GROUP BY zipcode

Answer 2

SELECT
 zipcode,
 COUNT(DISTINCT county)
FROM
 zipcode
GROUP BY
 zipcode
HAVING
 COUNT(DISTINCT county) > 1

Answer 3

Note that a 2005 ZIP table will be quite obsolete, since the USPS updates that file monthly. Most ZIP tables are derived from the USPS city-state file, which only lists the PREDOMINANT county for each five-digit ZIP. The USPS ZIP+4 database will identify all the counties that share more than one ZIP. As of May 2010, 9,944 of the 42,068 ZIP codes (over 23%) cross county boundaries.

http://semaphorecorp.com is a cheap source of current USPS data.