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