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

[Michael_Schutte@xxxxxxxxxxxx]

Unfortunately you are gonna have to group by a substring of site id.  Which
will slow your statement.

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 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.