This should work fine with 6.5:
SELECT City, Zip=substring(Zip,1,5), MBRs=count(Zip)
FROM MasterAccounts
WHERE Active = 1 /* Display only "active" people) */
GROUP BY substring(Zip,1,5), City
ORDER BY substring(Zip,1,5), City
Josh
*********** REPLY SEPARATOR ***********
On 2/4/00, at 2:01 PM, Nathan Haywood wrote:
>Hmmm... Did you copy it line for line? What version of SQL are you running?
>Try this:
>
>SELECT City, ZipCode=LEFT(Zip,5), MBRs=COUNT(Zip)
>FROM MasterAccounts
>WHERE Active = 1 /* Display only "active" people) */
>GROUP BY LEFT(Zip,5), City
>ORDER BY LEFT(Zip,5), City
>
>-----Original Message-----
>From: PowerNet [mailto:psc5@powersupply.net]
>Sent: Friday, February 04, 2000 1:52 PM
>To: emerald@iea-software.com
>Subject: RE: [Emerald] SQL QUERY
>
>
>Didn't work.
>
>Get this error:
>
>Msg 156, Level 15, State 1
>Incorrect syntax near the keyword 'LEFT'.
>
>
>*********** REPLY SEPARATOR ***********
>
>On 2/4/00, at 1:43 PM, Nathan Haywood wrote:
>
>>Sure...
>>
>>SELECT City, Zip=LEFT(Zip,5), MBRs=count(Zip)
>>FROM MasterAccounts
>>WHERE Active = 1 /* Display only "active" people) */
>>GROUP BY LEFT(Zip,5), City
>>ORDER BY LEFT(Zip,5), City
>>
>>
>>-----Original Message-----
>>From: keitenmiller@hughestech.net [mailto:keitenmiller@hughestech.net]
>>Sent: Friday, February 04, 2000 12:27 PM
>>To: emerald@iea-software.com
>>Subject: Re: [Emerald] SQL QUERY
>>
>>
>>Josh,
>>Looks good, but differentiates zip codes between 61111 and 61111-1111.
>>Is there a way to trim all charaters after the first 5 in the zip code
>>field?
>>Thanks,
>>Ken
>>
>>For more information about this list (including removal) go to:
>>http://www.iea-software.com/support/maillists/liststart
>
>For more information about this list (including removal) go to:
>http://www.iea-software.com/support/maillists/liststart
>
>For more information about this list (including removal) go to:
>http://www.iea-software.com/support/maillists/liststart
For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart
For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart