CODEWITHSUNDEEP
sqloracle-database
Marc Rasmussen
Marc Rasmussen

Reputation: 20545

Oracle SQL dividing two self defined columns

if i have the following select two count cases:

 COUNT(CASE WHEN STATUS ='Færdig' THEN 1 END) as completed_callbacks,
 COUNT(CASE WHEN SOLVED_SECONDS /60 /60 <= 2 THEN 1 END) as completed_within_2hours

and i want to devide the two results with eachother how can i achieve this?

this is my attemt however that failed:

 CASE(completed_callbacks / completed_within_2hours * 100) as Percentage

i know this is a rather simple question but i havnt been able to find the answer anywhere

Upvotes: 1

Views: 10213

Answers (3)

xlecoustillier
xlecoustillier

Reputation: 16351

You have to create a derived table:

SELECT completed_callbacks / completed_within_2hours * 100
FROM   (SELECT Count(CASE
                       WHEN status = 'Færdig' THEN 1
                     END) AS completed_callbacks,
               Count(CASE
                       WHEN solved_seconds / 60 / 60 <= 2 THEN 1
                     END) AS completed_within_2hours
        FROM   yourtable
        WHERE  ...)

Upvotes: 4

Ren&#233; Nyffenegger
Ren&#233; Nyffenegger

Reputation: 40489

select
   COUNT(CASE WHEN STATUS ='Færdig' THEN 1 END) 
  /
   COUNT(CASE WHEN SOLVED_SECONDS /60 /60 <= 2 THEN 1 END) 
  * 100 
  as 
   Percentage

Upvotes: 1

tbone
tbone

Reputation: 15473

Try this:

with x as (
  select 'Y' as completed, 'Y' as completed_fast from dual
  union all
  select 'Y' as completed, 'N' as completed_fast from dual
  union all
  select 'Y' as completed, 'Y' as completed_fast from dual
  union all
  select 'N' as completed, 'N' as completed_fast from dual
)
select 
sum(case when completed='Y' then 1 else 0 end) as count_completed,
sum(case when completed='N' then 1 else 0 end) as count_not_completed,
sum(case when completed='Y' and completed_fast='Y' then 1 else 0 end) as count_completed_fast,
case when (sum(case when completed='Y' then 1 else 0 end) = 0) then 0 else
  ((sum(case when completed='Y' and completed_fast='Y' then 1 else 0 end) / sum(case when completed='Y' then 1 else 0 end))*100)
end pct_completed_fast
from x;

Results:

"COUNT_COMPLETED"   "COUNT_NOT_COMPLETED"   "COUNT_COMPLETED_FAST"  "PCT_COMPLETED_FAST"
3   1   2   66.66666666666666666666666666666666666667

The trick is to use SUM rather than COUNT, along with a decode or CASE.

Upvotes: 1

Related Questions