Using only First 5 characters of a field in SQL statement

[FoxFool]

Is there a way to use a query statement like this..?

SELECT LEFT(ZIPCODE,5), COUNT(*) FROM MYTABLE GROUP BY LEFT(ZIPCODE,5)

I've also tried this..

lcMyzip=left(zipcode,5)
SELECT &lcMyzip, count(*) from mytable group by &lcMyzip

I'm running VFP 6 and it says that the SQL column ' ' does not exist. I've just been making a new field using the first 5 characters then running the query. Thanks

 

[baltman]

Has to do with using the function in the group by clause.

Brian

SELECT LEFT(ZIPCODE,5) as ZIP5, COUNT(*) FROM MYTABLE GROUP BY ZIP5

or

SELECT LEFT(ZIPCODE,5), COUNT(*) FROM MYTABLE GROUP BY 1