CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Daniela
Daniela

Reputation: 491

How To Divide Two Values From Different Tables in Select

I need to divide two tables, nr1 and nr2 like shown below

SELECT COUNT(candidate.id) as nr1
FROM candidate
WHERE candidate.id=2



select count (candidate.id) as nr2
from candidate 
where candidate.id=2 or candidate.id = 3;

select nr1/nr2 from nr1, nr2;

The problem is they don't existe outside the select query.

Upvotes: 3

Views: 10764

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I would approach this using conditional aggreation:

select (sum(case when c.id = 2 then 1.0 else 0 end) /
        sum(case when c.id in (2, 3) then 1.0 else 0 end)
       ) 
from candidate;

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

In oracle you can create a cte.

With table1 as ( 
      SELECT COUNT(candidate.id) as nr1
      FROM candidate
      WHERE candidate.id=2
), 
table2 as (
     select count (candidate.id) as nr2
     from candidate 
     where candidate.id=2 or candidate.id = 3
)
SELECT table1.nr1 / table2.nr2
FROM table1
CROSS JOIN table2

But a simple way should be using conditional SUM also you have to validate for div 0 cases.

WITH cte AS (
  SELECT         
       SUM(CASE WHEN candidate.id = 2 THEN 1 ELSE 0 END) as nr1,          
       SUM(CASE WHEN candidate.id IN (2,3) THEN 1 ELSE 0 END) as nr2
  FROM candidate
)
SELECT (CASE WHEN nr2 > 0 THEN nr1 / nr2 END) as result
FROM cte

Upvotes: 4

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

You can do it in single query. Aggregate the data conditionally in SELECT.

SELECT COUNT(CASE WHEN id = 2  THEN 1 END) 
       / COUNT(CASE WHEN id = 2 || id = 3 THEN 1 END) 
FROM candidate

Upvotes: 4

Related Questions