Re: Replacing GROUP BY with Sub-queries

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1067466836.934863_at_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,

The solution is for you to put the word "distinct" between the worlds "select" and "table_name" and that will give you the results you want.

For information about "distinct", I suggest you read the documentation available at

http://tahiti.oracle.com

It is a good idea to list your software version(s) when posting.

Thanks,
Vlad Received on Wed Oct 29 2003 - 19:38:54 CST