Re: Percentages in SQL?

In article <l.carl.pedersen-240194120237_at_kip-sn-120.dartmouth.edu> l.carl.pedersen_at_dartmouth.edu (L. Carl Pedersen) writes:
>In article <2hovq0$i83_at_elna.ethz.ch>, deboer@wsl34 (Gert de Boer) wrote:
>
>> Dear Oraclegroupers,
>>
>> How would you solve the following problem in pure sql?
>>
>> I want to count the records in a table, grouped by one
>> attribute. So I get a list like:
>>
>> 1 10
>> 2 150
>> 3 30
>> 4 10
>>
>> Now I want the same list, but not the counts, but as
>> percentages, like:
>>
>> 1 5.0
>> 2 75.0
>> 3 15.0
>> 4 5.0
>>
>> I know of solutions with help of views (but I once heard
>> that anything you can do with views, can be done in one
>> SQL-statement. Does somebody know any prove of this?),
>> or with use of variables in SQL*Plus, but that is not
>> what I am searching for.
>>
>> Any ideas?
>>
>> Gert de Boer
>>
>> deboer_at_wsl.ethz.ch
>
>select a, count(*) from t group by a;
>
>create view c as select count(*) t from t;
>
>select a, 100*count(*)/t from t, c group by a,t;
>
>I have heard the statement that views are not really necessary. I don't
>believe it.
>
>I think there are tricky ways to do a lot of things without views that
>might appear to need views, but I think it breaks down eventually.
>
... stuff deleted

In oracle v7 there is a table sys.tab$ which contains the number of rows in a specific table. This table is also appearing as a part of the view USER_TABLES and of course DBA_TABLES. Of course this value is incorrect if you don't do a analyze table compute statistics...

So with the following sql statement just after the analyze table... statement could do the trick :

select a, 100*count(*)/NUM_ROWS
from table XYZ, USER_TABLES
where USER_TABLES.TABLE_NAME = 'XYZ'
group by a;

Gerrit.

Gerrit.

-- 
Gerrit Cap				
Vlaamse Televisie Maatschappij N.V.		e-mail :   gerrit_at_vtm.be
Medialaan 1					fax    : +32 2 253.12.21
B-1800 Vilvoorde Belgium			voice  : +32 2 255.38.72