I am trying to write what I thought would be a fairly simple query but is proving to be harder than I thought. I want to divide the results of two select statements:
Select count (*) from tableA where columnA = 'A'
/
Select count (*) from tableA where columnA in ('1','2')
I am using Oracle SQL Developer
Thanks for any guidance
I would recommend conditional aggregation . . . but with sum() not count():
Select (sum(case when columnA = 'A' then 1 else 0 end) /
sum(case when columnA in ('1', '2') then 1 end)
)
from tableA;
Note the lack of else in the denominator. This handles the divide-by-zero case.
You could treat the, both as subqueries within a single main query:
select
(select count (*) from tableA where columnA = 'A')
/
(select count (*) from tableA where columnA in ('1', '2'))
from dual
or if the table and column names are actually the same you could do a single query with conditional counts, something like:
select count (case when columnA = 'A' then columnA end)
/
count (case when columnA in ('1', '2') then columnA end)
from tableA
where columnA in ('A', '1', '2')
I've fixed the single quotes in both, not sure if those were just an issue with posting your question.
You may also need to add logic to handle the second count being zero, if that could happen - as that would cause a runtime error.
