Re: Percentages in SQL?

From: Tremblay Michel <tremblam_at_JSP.UMontreal.CA>
Date: Tue, 25 Jan 1994 20:50:30 GMT
Message-ID: <CK7Dw8.8n6_at_cc.umontreal.ca>


In article <l.carl.pedersen-240194120237_at_kip-sn-120.dartmouth.edu> l.carl.pedersen_at_dartmouth.edu (L. Carl Pedersen) writes:
>
>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.
>
 

you can use count(distinct t2.rowid) to get "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.
 

select t1.a, count(distinct t1.rowid)/count(distinct t2.rowid) * 100 from t t1, t t2
group by t1.a  

I know, it give a bad performance with more than 50 rows in t, like you said it's just acadamic exercise.  

Michel TREMBLAY (tremblam_at_jsp.umontreal.ca) Received on Tue Jan 25 1994 - 21:50:30 CET

Original text of this message