Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Creating virtual keys to join by

Re: Creating virtual keys to join by

From: IANAL_VISTA <IANAL_Vista_at_hotmail.com>
Date: Sat, 23 Jul 2005 16:46:07 GMT
Message-ID: <Xns969C636112D59SunnySD@68.6.19.6>


nknight62_at_yahoo.com wrote in
news:1122133906.800878.286310_at_g43g2000cwa.googlegroups.com:

> I am always needing to do something like this, but can't seem to find
> the right approach:
>
> Summary: Join 1 table to itself to summarize data based on a substring
> of one of the fields. I have read-only access to the data.
>
> select unique substr(field1, 1,3) site , count(field1) mycount from
> db.table1 t1 join db.table1 t2 on site = substr(field1, 1,3) group by
> site;
>
> The problem is that "SITE" is an invalid identifier in the join and
> group clause. Using SQL*PLUS with Oracle 9i
>
> Regards,
> Nick
> Example Data
>
> Field1
> ABC223432432
> ABC234299392
> ABC899898988
> BBC230293090
> BBC2320020202
> BBC293920020
> BBC44343433
>
> Results Expected
> ABC 3
> BBC 4
>
>

SELECT SUBSTR(FIELD1,1,3), COUNT(SUBSTR(FIELD1,1,3)) FROM TABLE GROUP BY SUBSTR(FIELD1,1,3); Received on Sat Jul 23 2005 - 11:46:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US