Easy SQL, embedded select 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