# Get Average of 5 columns that may contains nulls

Hello Team,

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:

User | Q1 | Q2 | Q3 | Q4 | Q5 | Average |

Daniel | 10 | 7 | 8.5 |

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,

Daniel Marrujo

Hi Daniel,

Thank you for reaching out to us on this. I will review this and come back to you with some possible solutions. I will need to do some testing in order to make sure I am giving you a working example, but hopefully I can help steer you in the right direction.

Cheers,

Neal

Hi Daniel,

Thank you for reaching out to us on this. I will review this and come back to you with some possible solutions. I will need to do some testing in order to make sure I am giving you a working example, but hopefully I can help steer you in the right direction.

Cheers,

Neal

Hi Daniel,

Thank you for your patience on this. I have not been able to find a great in-built solution, so I have two FreehandSQL fields at the view level, and a standard Calculated field at the report level which will achieve what you are looking for. To do this, lets start with my view make up, including my FreehandSQL fields Numerator and Denominator:

The Numerator field has the following SQL:

Where Q1, Q2, etc are my column names from the DB. This sums them all together using a Null-to-Zero function.The Denominator field has the following SQL:

This provides us with a sum of the number of non-null values.Finally at the report level I have a simple Calculated field dividing the Numerator by the Denominator:

This gives me the resulting report:

Please let me know if this works for you, and if you have any questions.

Cheers,

Neal

Hi Daniel,

Thank you for your patience on this. I have not been able to find a great in-built solution, so I have two FreehandSQL fields at the view level, and a standard Calculated field at the report level which will achieve what you are looking for. To do this, lets start with my view make up, including my FreehandSQL fields Numerator and Denominator:

The Numerator field has the following SQL:

Where Q1, Q2, etc are my column names from the DB. This sums them all together using a Null-to-Zero function.The Denominator field has the following SQL:

This provides us with a sum of the number of non-null values.Finally at the report level I have a simple Calculated field dividing the Numerator by the Denominator:

This gives me the resulting report:

Please let me know if this works for you, and if you have any questions.

Cheers,

Neal

Thanks for your response however NVL is not currently supported..., this should be as simple as it is done on Excel as it excludes the nulls automatically when calculating an average.

Any other suggestions here?

Thanks for your response however NVL is not currently supported..., this should be as simple as it is done on Excel as it excludes the nulls automatically when calculating an average.

Any other suggestions here?

Hi Daniel,

I will need to confirm with one of our consultants on another way forward. Can I ask why NVL is not supported? What version of Oracle is this database using?

Cheers,

Neal

Hi Daniel,

I will need to confirm with one of our consultants on another way forward. Can I ask why NVL is not supported? What version of Oracle is this database using?

Cheers,

Neal

This is Smart Reporting on BMC, so the database is the AR SYSTEM

This is Smart Reporting on BMC, so the database is the AR SYSTEM

Hi Daniel,

I have done some more work on this and found the following two FreehandSQL Calculated fields (which I created at the view level for simplicity) and one Simple Calculated field will achieve this:

Field Name: Numerator

Freehand SQL:

Field Name: DenominatorFreehand SQL:

Field Name: AverageSimple Field: Numerator / Denominator

Let me know if this will work for you and how it goes.

Cheers,

Neal

Hi Daniel,

I have done some more work on this and found the following two FreehandSQL Calculated fields (which I created at the view level for simplicity) and one Simple Calculated field will achieve this:

Field Name: Numerator

Freehand SQL:

Field Name: DenominatorFreehand SQL:

Field Name: AverageSimple Field: Numerator / Denominator

Let me know if this will work for you and how it goes.

Cheers,

Neal

Hi Daniel,

I hope you are well. I am checking in to see if the suggestion I provided previously was able to assist in this? Was there anything else I could do to help here?

Cheers,

Neal

Hi Daniel,

I hope you are well. I am checking in to see if the suggestion I provided previously was able to assist in this? Was there anything else I could do to help here?

Cheers,

Neal

Hi Daniel,

I hope you have a great start to your week. I am checking in to see if the suggestion I provided previously was able to assist in this? Was there anything else I could do to help here?

Cheers,

Neal

Hi Daniel,

I hope you have a great start to your week. I am checking in to see if the suggestion I provided previously was able to assist in this? Was there anything else I could do to help here?

Cheers,

Neal

Hi Daniel,

I hope things are going well over there.

Just wanted to let you know I'll be closing this request due to inactivity. However, if you ever wanted to re-visit this or have anything else I can help you with, please let me know.

Cheers,

Neal

Hi Daniel,

I hope things are going well over there.

Just wanted to let you know I'll be closing this request due to inactivity. However, if you ever wanted to re-visit this or have anything else I can help you with, please let me know.

Cheers,

Neal

Replies have been locked on this page!