It would be appreciated explaining the internal functionality of SUM function in Oracle, when encountering null values:
The result of
select sum(null) from dual;
select sum(value) from
select case when mod(level , 2) = 0 then null else level end as value from dual
connect by level <= 10
select (1 + null) from dual
create table odd_table as select sum(null) as some_name from dual;
create table ODD_TABLE
SQL does not treat
NULL values as zeros when calculating
SUM, it ignores them:
Returns the sum of all the values, or only the
DISTINCTvalues, in the expression. Null values are ignored.
This makes a difference only in one case - when the sequence being totalled up does not contain numeric items, only
NULLs: if at least one number is present, the result is going to be numeric.