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(DISTINCT county)
FROM
    zipcode
GROUP BY
    zipcode
HAVING
    COUNT(DISTINCT county) > 1