Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Creating virtual keys to join by
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