Re: Dividing two Count(*)'s ??

On Wed, 07 Aug 1996 11:47:19 -0400, Bill Schoonmaker <bil.schoonmaker_at_bsis.com> wrote:

>Fernando Borcel wrote:
>>
>> How do I perform a query that returns the division between the count of two tables?
>>
>> That is to say:
>>
>> Select count(*) / (Select count(*) from Tb2) from Tb2;
>>
>> Thanks!
>>
>> Fernando Borcel
>
>You need to qualify each colunm and table... use this method
>
>SELECT COUNT(A.ANYCOLUMN)/COUNT(B.ANYCOLUMN)
> FROM TABLE1 A, TABLE2 B
>

No, this will always tell you how many rows are in A multiplied by the number of rows in B (eg: cartesian product).

For example

select count(a.username), count(b.user_id) from all_users a, all_users b;

COUNT(A.USERNAME)             COUNT(B.USERNAME)
------------------	      -------------------
               169                            169

select count(*) from all_users;

COUNT(*)

      13

(13*13 = 169).....

You can use inline views to do this:

select cnt_a/cnt_b
from ( select count(*) cnt_a from T1 ),

     ( select count(*) cnt_b from T2 )
/

This joins two counts, instead of counting a join....

>Hope this helps
>--
>===========================================================
>|Bill Schoonmaker email:bill.schoonmaker_at_bsis.com |
>|Oracle DBA |
>|Broadway & Seymour |

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Wed Aug 07 1996 - 00:00:00 CEST