New Topic

Category Report Builder

Product Version 8.0

Get Average of 5 columns that may contains nulls

Daniel Marrujo shared this question ago

Answered

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

Files:

The same question

Replies (9)

Neal Dennis ●

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

Reply URL

Neal Dennis ●

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:

927895972eb98c3340cf845806d63c91

The Numerator field has the following SQL:

(NVL(Q1,0) + NVL(Q2,0) + NVL(Q3,0) + NVL(Q4,0) + + NVL(Q5,0))

Where Q1, Q2, etc are my column names from the DB. This sums them all together using a Null-to-Zero function.

14a36735e05a9778e2f80422abd48838

The Denominator field has the following SQL:

(NVL2(Q1,1,0) + NVL2(Q2,1,0) + NVL2(Q3,1,0) + NVL2(Q4,1,0) + NVL2(Q5,1,0))

This provides us with a sum of the number of non-null values.

f102ee6c04a6fb26f504b7db42f91a6b

Finally at the report level I have a simple Calculated field dividing the Numerator by the Denominator:

b41a3d17bbe587f417c1d8f85aa4795f

This gives me the resulting report:

1030d06b27c3bc004c6fc473ade21a07

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

Cheers,

Neal

Hi Daniel,

927895972eb98c3340cf845806d63c91

The Numerator field has the following SQL:

(NVL(Q1,0) + NVL(Q2,0) + NVL(Q3,0) + NVL(Q4,0) + + NVL(Q5,0))

Where Q1, Q2, etc are my column names from the DB. This sums them all together using a Null-to-Zero function.

14a36735e05a9778e2f80422abd48838

The Denominator field has the following SQL:

(NVL2(Q1,1,0) + NVL2(Q2,1,0) + NVL2(Q3,1,0) + NVL2(Q4,1,0) + NVL2(Q5,1,0))

This provides us with a sum of the number of non-null values.

f102ee6c04a6fb26f504b7db42f91a6b

Finally at the report level I have a simple Calculated field dividing the Numerator by the Denominator:

b41a3d17bbe587f417c1d8f85aa4795f

This gives me the resulting report:

1030d06b27c3bc004c6fc473ade21a07

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

Cheers,

Neal

Reply URL

Daniel Marrujo ●

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?

Reply URL

Neal Dennis ●

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

Reply URL

Daniel Marrujo ●

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

Reply URL

Neal Dennis ●

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:

((CASE WHEN Q1 IS NULL THEN 0 ELSE Q1 END) + (CASE WHEN Q2 IS NULL THEN 0 ELSE Q2 END) + (CASE WHEN Q3 IS NULL THEN 0 ELSE Q3 END) + (CASE WHEN Q4 IS NULL THEN 0 ELSE Q4 END) + (CASE WHEN Q5 IS NULL THEN 0 ELSE Q5 END))

Field Name: Denominator

Freehand SQL:

((CASE WHEN Q1 IS NULL THEN 0 ELSE 1 END) + (CASE WHEN Q2 IS NULL THEN 0 ELSE 1 END) + (CASE WHEN Q3 IS NULL THEN 0 ELSE 1 END) + (CASE WHEN Q4 IS NULL THEN 0 ELSE 1 END) + (CASE WHEN Q5 IS NULL THEN 0 ELSE 1 END))

Field Name: Average

Simple 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:

((CASE WHEN Q1 IS NULL THEN 0 ELSE Q1 END) + (CASE WHEN Q2 IS NULL THEN 0 ELSE Q2 END) + (CASE WHEN Q3 IS NULL THEN 0 ELSE Q3 END) + (CASE WHEN Q4 IS NULL THEN 0 ELSE Q4 END) + (CASE WHEN Q5 IS NULL THEN 0 ELSE Q5 END))

Field Name: Denominator

Freehand SQL:

((CASE WHEN Q1 IS NULL THEN 0 ELSE 1 END) + (CASE WHEN Q2 IS NULL THEN 0 ELSE 1 END) + (CASE WHEN Q3 IS NULL THEN 0 ELSE 1 END) + (CASE WHEN Q4 IS NULL THEN 0 ELSE 1 END) + (CASE WHEN Q5 IS NULL THEN 0 ELSE 1 END))

Field Name: Average

Simple Field: Numerator / Denominator

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

Cheers,

Neal

Reply URL

Neal Dennis ●

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

Reply URL

Neal Dennis ●

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

Reply URL

Neal Dennis ●

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

Reply URL