Counting non-null columns in a rather strange way

Question

I have a table which has 32 columns. 2 of these columns are identity columns and the rest are values. I would like to get the average of all these 30 columns, excluding the null ones. If none of the columns were null, that would have been pretty easy as follows select (val0 + val1 + val2 + ...) / 30

Since I would like to exclude the null ones from the sum and divide, I shall rewrite the query similar to the one below (assuming oracle syntax, but same is possible with many other DBs. Just replace NVL with ISNULL for SQL Server, and IFNULL for MySQL):

select (nvl(val0, 0) + nvl(val1, 0) + ...) / "nonZeroColumnCountInThisRow"

The problem is I can not find a way to get the "nonZeroColumnCountInThisRow". Take the following:

"nonZeroColumnCountInThisRow" = ifNullThenZeroElse1(val0) + ifNullThenZeroElse1(val1) ...

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.

Could you please help.

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

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".

Answer 4

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.