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?


It seems that you would like to reference ZipCode in the OUTER query from the scalar subquery. You cannot reference the alias z since it has only just been defined in the same SELECT clause. The correct way to do this is just to use the base column, but to alias the table differently to prefix the column names with the aliases.

select a.ZipCode as z, 
  (select AVG(b.Latitude)
     from tlkp_ZipCodes b
    where b.ZipCode like LEFT(a.ZipCode, 4) + '%' ) AvgLatitude
 from tlkp_ZipCodes a

You can also use this, if your DBMS supports window functions:

SELECT ZipCode AS z, 
       AVG(Latitude) OVER (PARTITION BY LEFT(ZipCode, 4)) AS AvgLatitude
FROM tlkp_ZipCodes AS a ;

Test in SQL-Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.