Re: Replacing GROUP BY with Sub-queries

Daniel Morgan wrote:
> 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,

I'm sure you realize the problem is that some tables have multiple indexes so the second query reports the counts multiple times. To really be equivalent to the group by, I think you'd need something like this ...

SELECT table_name, (

   SELECT COUNT(*)
   FROM all_indexes ai2
   WHERE ai2.table_name = ai1.table_name) TAB_CNT FROM (
   select distinct table_name
   from all_indexes) ai1;

or

SELECT distinct table_name, (

   SELECT COUNT(*)
   FROM all_indexes ai2
   WHERE ai2.table_name = ai1.table_name) TAB_CNT FROM all_indexes ai1;

Or you could drive from ALL_TABLES instead but that would give zero counts from tables with no indexes (the group by query doesn't).

--
Richard Kuhler