Counting non-null columns in a rather strange way

Question

I have a table which has 32 columns in an Oracle table.

• Two of these columns are identity columns
• the rest are values

I would like to get the average of all the value columns, which is complicated by the null (identity) columns. Below is the pseudocode for what I am trying to achieve:

    SELECT 
           ((nvl(val0, 0) + nvl(val1, 0) + ... nvl(valn, 0)) 
           / nonZero_Column_Count_In_This_Row)

Such that: nonZero_Column_Count_In_This_Row = (ifNullThenZeroElse1(val0) + ifNullThenZeroElse1(val1) ... ifNullThenZeroElse(valn))

The difficulty here is of course in getting 1 for any non-null column. It seems I need a function similar to NVL, but with an else clause. Something that will return 0 if the value is null, but 1 if not, rather than the value itself.

How should I go about about getting the value for the denominator?

---

PS: I feel I must explain some motivation behind this design. Ideally this table would have been organized as the identity columns and one value per row with some identifier for the row itself. This would have made it more normalized and the solution to this problem would have been pretty simple. The reasons for it not to be done like this are throughput, and saving space. This is a huge DB where we insert 10 million values per minute into. Making each of these values one row would mean 10M rows per minute, which is definitely not attainable. Packing 30 of them into a single row reduces the number of rows inserted to something we can do with a single DB, and the overhead data amount (the identity data) much less.

Answer 1

(Case When col is null then 0 else 1 end)

Answer 2

You could use NVL2(val0, 1, 0) + NVL2(val1, 1, 0) + ... since you are using Oracle.

Answer 3

Another option is to use the AVG function, which ignores NULLs:

SELECT AVG(v) FROM (
WITH q AS (SELECT val0, val1, val2, val3 FROM mytable)
SELECT val0 AS v FROM q
UNION ALL SELECT val1 FROM q
UNION ALL SELECT val2 FROM q
UNION ALL SELECT val3 FROM q
);

If you're using Oracle11g you can use the UNPIVOT syntax to make it even simpler.

Answer 4

I see this is a pretty old question, but I don't see a sufficient answer. I had a similar problem, and below is how I solved it. It's pretty clear a case statement is needed. This solution is a workaround for such cases where

SELECT COUNT(column) WHERE column {IS | IS NOT} NULL

does not work for whatever reason, or, you need to do several

SELECT   COUNT ( * )
  FROM   A_TABLE
 WHERE   COL1 IS NOT NULL;

SELECT   COUNT ( * )
  FROM   A_TABLE
 WHERE   COL2 IS NOT NULL;

queries but want it as a data set when you run the script. See below; I use this for analysis and it's been working great for me so far.

SUM(CASE NVL(valn, 'X')
                WHEN 'X'
                THEN 0 
                ELSE 1 
                END) as COLUMN_NAME
            FROM YOUR_TABLE;

Cheers!
Doug

Answer 5

Generically, you can do something like this:

SELECT (
       (COALESCE(val0, 0) + COALESCE(val1, 0) + ...... COALESCE(valn, 0))
       /
       (SIGN(ABS(COALESCE(val0, 0))) + SIGN(ABS(COALESCE(val1, 0))) + .... )
       ) AS MyAverage

The top line will return the sum of values (omitting NULL values) whereas the bottom line will return the number of non-null values.

FYI - it's SQL Server syntax, but COALESCE is just like ISNULL for the most part. SIGN just returns -1 for a negative number, 0 for zero, and 1 for a positive number. ABS is "absolute value".