August 15, 2016 at 10:48 am
We've got to determine the most populated zip code in each county, in our state (New Mexico). I've been trying to research this, but for some reason (and no one is talking about what that reason is) we're having network issues today. Sometimes we can get to the Internet (like at this moment while I type this post), but then we loose it all. I've tried 3 browsers - all experiencing the same problem. Rebooted my PC twice, in case that was the issue.
Anyway, thought I'd post this. At least email will wait.
Kindest Regards, Rod Connect with me on LinkedIn.
August 15, 2016 at 3:00 pm
Just slapped this together, but you should be able to get this to work:
;with cte as (
select county, zipcode, count(*) as cnt
from table
group by county, zipcode)
select county, zipcode, cnt, row_number() over(partition by county, zipcode order by cnt desc) as rownum
from cte
where rownum = 1
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 16, 2016 at 9:19 am
Rod at work (8/16/2016)
Thank you, Kevin. I agree that looks like it will work. The question I have is, where do I get the data in the first place?(Internet connectivity is much better today.)
I would start with US Census as part of your search. There are probably paid resources with all you need (and more) as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 16, 2016 at 9:56 am
Hi Rod,
Google: filetype:xlsx new mexico counties population
http://www.census.gov/popest/data/intercensal/county/tables/CO-EST00INT-01/CO-EST00INT-01-35.xls
As for your network connectivity issues, please email help desk support. 🙂
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 17, 2016 at 8:37 am
TheSQLGuru (8/16/2016)
Rod at work (8/16/2016)
Thank you, Kevin. I agree that looks like it will work. The question I have is, where do I get the data in the first place?(Internet connectivity is much better today.)
I would start with US Census as part of your search. There are probably paid resources with all you need (and more) as well.
I'll be paying out of my own pocket then. We've got such a tight budget that they aren't paying for anything.
I'll take a look around and see what I can find. Thanks.
Kindest Regards, Rod Connect with me on LinkedIn.
August 17, 2016 at 1:14 pm
Check out the site http://www.geonames.org/. They have free and paid levels of data you can download. Be forewarned that the files are not small and it changes frequently, so you'll want to write your import processes as procedures that can be automated to run using text files on disk.
August 18, 2016 at 10:09 am
I hope it works for you. Thanks for the feedback.
August 19, 2016 at 5:10 am
Have you considered what you will do with zip codes that cross county lines? Does your data source (when you decide on one) split the zip code by county?
Consider 49423, Holland, MI. The city of Holland straddles the Ottawa/Allegan county line, with most of the city population in Ottawa. But 49423 extends farther south into Allegan outside the city limits. I'd guess that the Allegan portion of the zip code is the most populous in the county, even excluding the Ottawa part of the zip code. But if you look up 49423, most sources would list it as being in Ottawa, and you'd miss it entirely in Allegan.
August 19, 2016 at 7:32 am
gvoshol 73146 (8/19/2016)
Have you considered what you will do with zip codes that cross county lines? Does your data source (when you decide on one) split the zip code by county?Consider 49423, Holland, MI. The city of Holland straddles the Ottawa/Allegan county line, with most of the city population in Ottawa. But 49423 extends farther south into Allegan outside the city limits. I'd guess that the Allegan portion of the zip code is the most populous in the county, even excluding the Ottawa part of the zip code. But if you look up 49423, most sources would list it as being in Ottawa, and you'd miss it entirely in Allegan.
The possibility did cross my mind once, but I'd forgotten about it until you brought it up. The table involved doesn't have a unique constraint on the zip code column, so I think we'll be OK.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply