SQL query to count and list the different counties per zip code
- by Chris
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.
