|
That's not what he wanted. That would give himWhich
SUBSTRING function ITEMNUMBER COUNT
A NHZF500-051 1
E NHZF500-051 1
J NHZF500-051 3
******** End of data ********
Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From:
Michael_Schutte@xxxxxxxxxxxx
To:
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date:
01/21/2009 04:33 PM
Subject:
Re: SQL: How to get number of records (based on a prefix)
Sent by:
midrange-l-bounces@xxxxxxxxxxxx
Unfortunately you are gonna have to group by a substring of site id.
will slow your statement.site
Select substring(Siteid,1,1), itemnumber, count(itemnumber) from file
group by substring(siteid,1,1), itemnumber
Michael Schutte
Admin Professional
midrange-l-bounces@xxxxxxxxxxxx wrote on 01/21/2009 04:20:44 PM:
Hi list:
I want to use SQL on this one. I have a file with item numbers and
physicalids.
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
thelocation. I really do not care about the last 2 positions, only on
caseprefix 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
brave3).
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
--------------------------------------------------------------------------------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.
list
--list
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
