Easy SQL, embedded select

Question

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?

孔夫子 · Accepted Answer · 2012-10-26 22:31:00Z

1

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

answered Oct 26, 2012 at 22:31

孔夫子

4,3403 gold badges31 silver badges50 bronze badges

Add a comment |

ypercubeᵀᴹ · Accepted Answer · 2012-10-27 10:45:01Z

1

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

answered Oct 27, 2012 at 10:45

ypercubeᵀᴹ

100k13 gold badges217 silver badges306 bronze badges

Add a comment |

Stack Exchange Network

Easy SQL, embedded select

2 Answers 2

Your Answer

Hot Network Questions

Easy SQL, embedded select

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions