group by a subtring?

[pinepond]

I have a table of zip codes that I want to group in two ways
(1) by all five characters
(2) by the first 3 characters

so that I can create pre-sort bulk mail bundles...

can anyone help with teh sql

to group for the entire zip I am using

select zip,count(*) for 'ziptable' group by zip

[Xnuiem]

use the substring() function.

select zip, substring(zip, 3) as f from ziptable group by f

I dont remember the exact syntax but you get the idea.

[pinepond]

thanks that works... is there a way to limit the results to only those that have a count > 10 or some number?

[Xnuiem]

Im not sure if I understand that question.

You only want the results if there are more than 10 of that result in the table?

[barand]

SELECT SUBSTRING(zipcode, 1, 3) as f, COUNT(*) as num FROM ziptable GROUP BY f HAVING num > 10