CODEWITHSUNDEEP
sqloracle-databasepercentagedivide
Marcus
Marcus

Reputation: 3869

How to divide two select query results and calculate the percentage

I have below 2 select queries. I want to divide the result of first select query result with the second select query result and multiply this result with 100 in order to calculate the percentage. But dont know how to do this.

select count(*) from rate_errors where id > to_char(SYSTIMESTAMP - INTERVAL '1' HOUR,'YYYYMMDDHH24MISS')||'0000'

select count(*) from SDR_O2 where id > to_char(SYSTIMESTAMP - INTERVAL '1' HOUR,'YYYYMMDDHH24MISS')||'0000'

Upvotes: 1

Views: 8486

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I would be inclined to do this with a cross join:

select r.cnt / o.cnt
from (select count(*) as cnt
      from rate_errors
      where id > to_char(SYSTIMESTAMP - INTERVAL '1' HOUR, 'YYYYMMDDHH24MISS') ||'0000'
     ) r cross join
     (select count(*) as cnt
      from SDR_O2
      where id > to_char(SYSTIMESTAMP - INTERVAL '1' HOUR, 'YYYYMMDDHH24MISS')||'0000'
    ) o;

If you want, you can break out the id calculation to a CTE:

with params as (
      select to_char(SYSTIMESTAMP - INTERVAL '1' HOUR, 'YYYYMMDDHH24MISS')||'0000'
      from dual
     )
select r.cnt / o.cnt
from (select count(*) as cnt
      from params cross join rate_errors re
      where re.id > params.id
     ) r cross join
     (select count(*) as cnt
      from params cross join SDR_O2 o
      where o.id > params.id
    ) o;

This makes it easier to change the id and ensures that the logic is the same for both subqueries.

Upvotes: 6

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

with x as 
(select count(*) rcnt from rate_errors where id > to_char(SYSTIMESTAMP - INTERVAL '1' HOUR,'YYYYMMDDHH24MISS')||'0000')
, y as (select count(*) scnt from SDR_O2 where id > to_char(SYSTIMESTAMP - INTERVAL '1' HOUR,'YYYYMMDDHH24MISS')||'0000')
select 100*rcnt/scnt from x, y;

You can do this using cte's. However if there exists no relation between x and y above, you can't be sure of the result.

Upvotes: 1

Related Questions