Troubleshooting Errors with Two SUMs

Question

I have a table, it's going to be used for a supplier scorecard, with eleven different fields that can be assigned a value of 1-5. Null values are allowed.

I need to write a query that will calculate the average of the fields that are filled out by each row. In other words, I might be dividing TOTAL by 11 in one row, and dividing TOTAL by 5 in another.

I'm working with this query:

select 
cf$_vendor_no, 
cf$_party,  
cf$_environmental, 
cf$_inspections, 
cf$_invoice_process, 
cf$_ncr, 
cf$_on_time_delivery, 
cf$_qms, 
cf$_safety, 
cf$_schedule, 
cf$_scope_of_work, 
cf$_turn_times,
sum(nvl(cf$_environmental,0)
+nvl(cf$_inspections,0)
+nvl(cf$_invoice_process,0)
+nvl(cf$_ncr,0)
+nvl(cf$_on_time_delivery,0)
+nvl(cf$_qms,0)
+nvl(cf$_safety,0)
+nvl(cf$_schedule,0)
+nvl(cf$_scope_of_work,0)
+nvl(cf$_turn_times,0)) 
/
sum(
case when cf$_environmental is not null then 1 else 0 end +
case when cf$_inspections is not null then 1 else 0 end +
case when cf$_invoice_process is not null then 1 else 0 end +
case when cf$_ncr is not null then 1 else 0 end +
case when cf$_on_time_delivery is not null then 1 else 0 end +
case when cf$_qms is not null then 1 else 0 end +
case when cf$_safety is not null then 1 else 0 end +
case when cf$_schedule is not null then 1 else 0 end +
case when cf$_scope_of_work is not null then 1 else 0 end +
case when cf$_turn_times is not null then 1 else 0 end) --as "average"
from supplier_scorecard_clv  
group by cf$_vendor_no, cf$_party, cf$_environmental, cf$_inspections, cf$_invoice_process, cf$_ncr, cf$_on_time_delivery, cf$_qms, cf$_safety, cf$_schedule, cf$_scope_of_work, cf$_turn_times

And, it almost works.

The first SUM in my code will add the values in each row to give me a total. I get a total 25 for the first FARW002 row, I get 6 for the second, and 12 for the third.

The second SUM in my code works as well. I get a count of 6 for my first FARW002 row, 2 for my second, and 3 for my third.

However, when I try to combine these, like in the code snippet above, I get a "ORA-00923: FROM keyword not found where expected" error and I'm not sure why.

Answer 1

So, this is stupid but here's what the problem ended up being:

 +nvl(cf$_turn_times,0))  
/ 
sum( 

When I changed the code to this - really I was just dicking around - it worked:

+nvl(cf$_turn_times,0))/sum(

So, something about having the / and SUM separated from the rest of the query - which I only do to make the code more readable for me - was causing the issue. Thanks for nothing Juan!