SQL Select & Count

[MikeL91]

I use this code to run a frequency:
select county,city,zip, cnt(*) as qty from testfile ;
group by county,city,zip into cursor mike

My problem is I get duplicate city names in my select file.
like this:
COUNTY CITY ZIP QTY
ORANGE ANAHEIM 92645 2
ORANGE ANAHEIM 92645 1
ORANGE BREA 92666 9


What am I not doing corrcty?

Thanks in advance,
Mike

 

[JimWWinter]

Mike,

Well that code would produce duplicate city names because cities can have multiple zip codes. The bigger question is why your getting duplicate zips. And I'm afraid I don't have an answer to that one.

Good luck,
Jim

 

[Stella740pl]

Couple of thoughts:

Try to rebuild index file of you source file (best of all, from scratch). Could be the reason of the problem.

Also, are you sure that city names/zip that seem the same are really identical in your source file?

 

[MikeL91]

More information. I apologize for not wording it better.

The problem is in fact that the ZIPS are showing duplicates. I need the output to look like that, with COUNTY,CITY,ZIP,QTY
however, I want the output to be unique on ZIP.

That code I showed produces this:
COUNTY CITY ZIP QTY
ORANGE ANAHEIM 92645 2
ORANGE ANAHEIM HILLS 92645 1
ORANGE BREA 92666 9


I need this:
COUNTY CITY ZIP QTY
ORANGE ANAHEIM 92645 3
ORANGE BREA 92666 9

I know it is working the way it should because the city's are not the same, but I guess I need to only show one version of the different city names to ensure that I only get I line per unique zip.

If that did not confuse you, It did me.

 

[Stella740pl]

In this case, you shouldn't group on the city. It's not identical entries, even with identical zips, so naturally it creates sepaprate entries for separate city names.

select county,city,zip,cnt(*) as qty ;
from testfile ;
group by county,zip ;
order by county,city,zip ;
into cursor mike

This is for VFP up to v.7. If you run VFP8 or 9, there is some difference in SELECT with GROUPing statements.

 

[alvechurchdata]

I guess I need to only show one version of the different city names to ensure that I only get I line per unique zip.

Which version of Fox are you using? SQL got strict in v8 and will only let you show aggregates and the fields on which you are grouping. You can use SET ENGINEBEHAVIOUR 70 to force VFP 8 to behave like VFP 7.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[JimWWinter]

Mike,

Just drop the city from your query and you should be all set.

Regards,
Jim

 

[MikeL91]

I'm on VFP8.0. I will try dumping the city.

Thanks everyone for your insight.

-Mike

 

[mkrausnick]

If you require unique zips then zip should be the ONLY field you group on. I've never heard of a zip code crossing county boundaries but it is theoretically possible.

Mike Krausnick
Dublin, California

 

[TamarGranor]

Yes, zip codes can cross county lines. Until my zip code was changed some years ago, it included part of two counties.

Tamar

 

[SGLong]

You can also run into zip codes that have multiple city names. These multiple city names are not necessarily "official", but you do encounter them.

My in-laws live in Upper St. Clair, Pennsylvania (Zip 15241) which is a suburb of Pittsburgh. According to the Post Office, all zip codes that begin with 152 are to be addressed "Pittsburgh, PA". The Upper St. Clair Commissioners have asked residents to use "Upper St. Clair, PA 15241" when referring to their addresses (sort of a prestige thing I think).

Mailing databases will use the official name based upon the number, but absent that it is possible to have multiple city names for a single zip code.

Steve