Get Average of 5 columns that may contains nulls
Here is the business case, we have some surveys (5 questions) that are not mandatory so some of the fields could be null and we need to get the average that ignore the null values as excel normally do, so we could have an output like this:
On this case the average is ignoring Q1, Q3 and Q5 and just doing (10+7)/2. however we cannot make it work on.
We try the following methods:
- Simply try to use a case statement with AVG (Q1 +Q2+Q3+Q4+Q5) however we get an erro message: ERROR (552): The SQL database operation failed.; Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
- Tried to construct some flags for example:
Case when Q1 is null then 0 else 1
Case when Q2 is null then 0 else 1
Case when Q3 is null then 0 else 1
Case when Q4 in null then 0 else 1
Case when Q5 is null then 0 else 1
With this we will get the number to divide the survey... however when trying to do a new case statement like sum ( Flag 1 +Flag 2 +Flag 3...) it dont work with the same error:
ERROR (552): The SQL database operation failed.; Cannot perform an aggregate
So we go with advance functions and work perfectly with sum of columns.
At this point we only need the numerator, so also get it with advance functions performing a sum of each of the 5 fields (advance functions because a case statement will give me same error)
Now I have Numerator and denominator using advance functions.... but cannot make another advance functions vs an advance function... so we are stuck...
- Try a very large case statement that Works when validating just 3 variables (Q1, Q2 ,Q3) however its too large if adding all the possible conditions, with an error message from the DB:
java.sql.SQLException: ORA-01704: string literal too long
Attaching large Case statement
Any other Suggestions?
Thanks in advance,