Hi,

I want to calculate the fraction of two counts and I have created the
following select statement:

select
field1,
field2,
(select table1.co/table2.co
from (select count(1) co, id from t_table1 where seal>=1000 group
by id) table1,
(select count(1) co, id from t_table2 group by id) table2
where table1.id=j.id and table2.id=j.id),
field3
from t_table3 j

This works as long as there are corresponding entries in t_table1 and
t_table2, otherwise the fraction becomes null, which might make sense
when there is nothing in t_table2 but not when t_table1 has no
matching entries.

Instead I have tried:

select
field1,
field2,
(select table1.co/table2.co
from (select count(1) co, id from t_table1 where seal>=1000 and
id=j.id) table1,
(select count(1) co, id from t_table2 where id=j.id) table2),
field3
from t_table3 j

But then the database (Oracle 10) complains that it can not find j.id.
How can I write this select correctly?

Best regards
Niels Dybdahl

On May 7, 6:40 am, Niels Dybdahl <niels.dybd... (AT) gmail (DOT) com> wrote:

Why make your statement so complicated?
Make an inline view. (basically move the subquery to the FROM clause.)
select
field1,
field2,
ratio,
field3
from t_table3 j,
(select table1.co/table2.co ratio
from (select count(1) co, id from t_table1 where seal>=1000 group
by id) table1,
(select count(1) co, id from t_table2 group by id) table2
where table1.id=table2.id) ratioview
where ratioview.id=j.id ;

For dealing with the NULL conditions, consider the NVL() function and
possibly an outer join between table1 and table2.

HTH,
Ed

PS ( for ORACLE specific questions like this, you should consider
posting to one of the oracle newsgroups. For SQL questions like this,
use comp.databases.oracle.server)

Hi Ed,

Thanks for your suggestion, but the problem is that the query:

select count(1) co, id from t_table1 where seal>=1000 group by id

Does not return any record for those ids that do not have records with
seal>=1000. So I get ratio=null in those cases instead of ratio=0.
Instead I have replaced it by the query:

select sum(case when (seal>=1000) then 1 else 0 end) co, id from
t_table1 group by id

That works much better. I still get a ratio=null for those ids that do
not have entries in table2, but I can live with that.

I tried NVL, but it seems not to help, because the problem is that I
do not get any record, while it would have helped if I got a record
containing a null value.

Would that work when I am already counting on table2?

Best regards
Niels Dybdahl

How about this?
select
field1,
field2,
(select count(*) from t_table1 where id=j.id and seal>=1000) /
(select NULLIF(count(*),0) from t_table2 where id=j.id) ratio,
field3
from t_table3 j;