Re: Replacing GROUP BY with Sub-queries

Vlad Kasparov wrote:

>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
>
>

"For 'tis the sport to have the enginer / Hoist with his owne petar"

Even Shakespeare (Hamlet) had the foresight to see if would be a software engineer.

-- 
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)