Re: Creating virtual keys to join by

From: Chris $ Val $ <chrisval_at_bigpond.com.au>
Date: Sun, 24 Jul 2005 03:55:34 GMT
Message-ID: <WiEEe.60496$oJ.10658@news-server.bigpond.net.au>

<nknight62_at_yahoo.com> wrote in message
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
Something like this should do thge trick:

COLUMN "Site" FORMAT A5
SELECT SUBSTR( FIELD1, 1, 3 ) as "Site", COUNT(*) as "Count" FROM table1
GROUP BY SUBSTR( FIELD1, 1, 3 ); Cheers,
Chris Val Received on Sat Jul 23 2005 - 22:55:34 CDT