How to compute percentage error 1

[rogers42]

Hi Folks,

I am looking for an elegant way of computing the percentage error. The following code works, but I don't want to repeat the same statment to compute the % error.

select NODE,
count(*) "Total_Errors",
sum(case when (CODE = 3) then 1 else 0 end) "Code_3",
(sum(case when (CODE = 3) then 1 else 0 end)/count(*))*100
from T;

The desired output should have the following columns

NODE Total_Errors Code_3 Percent_Error

Thanks in advance

rogers42

 

[SantaMufasa]

Rogers,

Your code is fundamentally sound, but it has one syntax error...you need "GROUP BY NODE" since your SELECT uses aggregate functions, COUNT and SUM:

select NODE,
   count(*) "Total_Errors",
   sum(case when (CODE = 3) then 1 else 0 end) "Code_3",
  (sum(case when (CODE = 3) then 1 else 0 end)/count(*))*100 "Percent_Error"
from T
group by node
/
NODE  TOTAL_ERRORS     CODE_3 PERCENT_ERROR
----- ------------ ---------- -------------
A                9          3    .333333333
B                8          3          .375

Restatement of your "sum(case when..." clause is not inefficient in Oracle.

If you truly want to get rid of the restatement, you can use the following technique, which relies upon an "in-line" VIEW:

select node,total_errors,code_3,code_3/total_errors Percent_error
  from (select NODE
              ,count(*) Total_errors
              ,sum(decode(code,3,1)) Code_3
          from T
         group by node)
/

NODE  TOTAL_ERRORS     CODE_3 PERCENT_ERROR
----- ------------ ---------- -------------
A                9          3    .333333333
B                8          3          .375

Let us know if this resolves your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”