Replacing GROUP BY with Sub-queries

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 29 Oct 2003 14:33:39 -0800
Message-ID: <1067466836.934863@yasure>

Avarma posted something a few days back that I have tried to turn into a demo for
my students with very bad results. Here's what I did:

The group by example

SELECT table_name, COUNT(*) TAB_CNT
FROM all_indexes
GROUP BY table_name;
-- returns 424 rows

Then I tried to emulate the example and did this.

SELECT table_name, (

   SELECT COUNT(*)
   FROM all_indexes ai2
   WHERE ai2.table_name = ai1.table_name) TAB_CNT FROM all_indexes ai1;
-- returns 573 rows

Unfortunately it does not produce the same result.

Any help offered will be greatly appreciated. What I need is a simple demo of a basic group by to a basic sub-query that runs with all_indexes or a similar DD view.

Thank you,
--

Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu

(replace 'x' with a 'u' to reply) Received on Wed Oct 29 2003 - 16:33:39 CST