Re: Percentages in SQL?

In article <2hovq0$i83_at_elna.ethz.ch>, deboer_at_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.

How, for example would we do this problem without views?

We could do an unrestricted join of t to itself, to get every row of t joined on.

select ... from t t1, t t2 group by t1.rowid;

In this case, COUNT(*) will give us the total count we need, but our result has too many rows. In order to get the proper number of rows, we need to group by A:

select ... from t t1, t t2 group by t1.a;

Now, we have lost the total number of rows in T.

Clearly, this is just an academic exercise, but if anyone has a proof of this one way or the other I would LOVE to see it. Received on Mon Jan 24 1994 - 18:02:37 CET