Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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
share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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