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

Interesting. This just came through yesterday from iSeries Network.

A SQL Tip for Counting Records
Article ID: 57662
Posted January 20th, 2009
in

* Database/SQL

Click here to find out more!

I just found this SQL tip not too long ago and could have used it
several times in the past to avoid creating a temporary file to count
records. You can count the number of distinct values of a field in a
file. For example, you have an inventory file where a single item is
in several locations and you need to know how many locations this item
exists in, and at the same time you can do other calculations, such as
summing up the on-hand quantity.

SELECT count(distinct location), sum(onhand)
FROM inventory
WHERE item = 'someitem#'

It will display the number of locations that this item exists in and
the total on hand for the item selected.

Thanks to Kim Barker of The Persimmon Group, Inc. for this tip!

On Wed, Jan 21, 2009 at 2:28 PM, Jeff Young <cooljeff913@xxxxxxxxx> wrote:

Peter,
Try this:
With Prefix as (select distinct itnbr,substr(stid,1,1) from IDFILE) select itnbr,count(*) from prefix order by itnbr



Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2
IBM Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3








________________________________
From: "Peter_Vidal@xxxxxxxx" <Peter_Vidal@xxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Sent: Wednesday, January 21, 2009 4:20:44 PM
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
MAPICS IT TECHNICAL SUPPORT TEAM / SR SYSTEM ANALYST
10540 Ridge Rd., Suite 203, New Port Richey, FL 34654-5111
Tel:727-849-9999, x2414 Fax:727-815-3120
WWW.PALL.COM

"Moral excellence comes about as a result of habit. We become just by
doing just acts, temperate by doing temperate acts, brave by doing brave
acts."
Aristotle

--------------------------------------------------------------------------------

Attention:

This communication may contain information that is confidential,
privileged and/or exempt from disclosure under applicable law.
If you are not the intended recipient, please notify the sender
immediately and delete the original, all attachments, and all
copies of this communication.


--------------------------------------------------------------------------------
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.