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.