I have a table of zip codes with latitudes / longitudes. Unfortunately it doesn't seem to have a few zip codes. I would like to try to approximate the latitude / longitude based on other zip codes that are at least in the area.
So for zip code 52733 which is not in my database, I was thinking of taking the average latitudes of all other zip codes that are "LIKE 5273%".
select ZipCode as z, (select AVG(Latitude) from tlkp_ZipCodes where ZipCode like LEFT(z, 4) + '%' ) from tlkp_ZipCodes
That doesn't seem to work though. It says "z" is an invalid column name. How do I do this?