How to divide two SELECT COUNT result?

Question

I use Oracle 11g and I want to divide 2 result from my SQL query :

SELECT ((SELECT COUNT(PK_id) FROM TAB1) / SELECT COUNT(TAB1.PK_id)
FROM TAB1
INNER JOIN TAB2 ON TAB2.PK_id = TAB1.FK_id
FROM TAB2;

I want to divide, with 2 SQL query result, the result from COUNT. On the first query, I count the number of ID I had, and in second, I make an inner join to count ID that I had in my TAB1 and on my TAB2..

Tancks. Alex

Answer 1

Remove the select before the 2nd count

SELECT ( (SELECT COUNT(PK_id) FROM TAB1) / COUNT(TAB1.PK_id) )
FROM TAB1
INNER JOIN TAB2 ON TAB2.PK_id = TAB1.FK_id
FROM TAB2;

Answer 2

You're trying to calculate the ration of records in TAB1 that has foreign key from TAB2? Is the foreign key enabled?

Can you not just say:

select count(pk_id)/ count(fk_id) 
-- counting pk_id will get you all records, and counting fk_id will got you records with fk_id not null
from tab1

However, you might want to protect against nulls and 0 in count

Answer 3

You could phrase this using a single query as:

SELECT
    COUNT(DISTINCT t1.PK_id) / COUNT(t1.PK_id) AS cnt_ratio
FROM TAB1 t1
INNER JOIN TAB2 t2
    ON t2.PK_id = t1.FK_id