Zip2County 1

[dave755]

I have two tables:
(1) Addr - a list of addresses, including zip codes. This table currently has ~800k rows, but we want to grow it to have ~6M rows.
(2) ZipToCounty - A cross reference of zip codes to the county that each zip code is in. There are 1124 zip codes in 83 counties.

What I want is a table of how many addresses are in each county.

I have been able to do it with this rather clunky bit of SQL using a cursor and a temporary table, but this strikes me as ham-handed and it is already slow.

declare @County as varchar(30)
declare @cnt as integer
declare cr cursor for 
  select distinct vcCounty 
    from Lookups.dbo.tblluZipToCounty 
    order by vcCounty
create table #CountyCount 
  (
   vcCounty varchar(30), 
   iCount integer
  )
open cr
fetch next from cr into @County
while @@fetch_status = 0 begin
  select @cnt=count(*) from dmvaddr as a
    left outer join Lookups.dbo.tblluZipToCounty as c
    on c.vcZip = a.zip where zip in 
      (select vcZip from Lookups.dbo.tblluZipToCounty
       where vcCounty = @County)
  insert into #CountyCount 
    (vcCounty, iCount) 
     values 
    (@County, @cnt)
  fetch next from cr into @County
end
close cr
deallocate cr
select vcCounty as 'County', iCount as 'Registrants' 
  from #CountyCount order by vcCounty

Is there a more elegant way to get my answer.

Dave Gee

 

[JamesLean]

Wow, talk about round the houses! ;-)

SELECT z.vccounty, COUNT(*) AS registrants
FROM dmvaddr d JOIN Lookups.dbo.tblluZipToCounty z ON d.zip = z.vczip
GROUP BY z.vccounty
ORDER BY z.vccounty

--James

 

[vongrunt]

Looks OK, though counties without addresses won't be listed because of inner join.

 

[dave755]

Thanks for the guidance. This was exactly what I was hoping to get with this posting.

Dave Gee