RE: How to get number of records (based on a prefix)

select item_number, count(distinct substr(site_id,1,1))
from myFile
group by item_number

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: SQL: How to get number of records (based on a prefix)

Hi list:

I want to use SQL on this one. I have a file with item numbers and site
ids.

ITNBR = 15 characters
STID = 3 characters

The prefix of the STID defines the physical location of the site and
positions 2 and 3 may bring more data about sub-groups of that physical
location. I really do not care about the last 2 positions, only on the
prefix of STID.

The data on the file looks like this:

Item Site
Number ID

NHZF500-051 A1P
NHZF500-051 E1P
NHZF500-051 J1D
NHZF500-051 J1E
NHZF500-051 J1P

After the execution of the SQL, I want to have the following results:

NHZF500-051 3

I need to have only one instance of the item number and also I need to
have the number of times the prefix changed for that item (in this case
3).

Thanks in advance!

PETER VIDAL