Mohsen Heydari Mohsen Heydari - 6 months ago 23
SQL Question

Why SUM(null) is not 0 in Oracle?

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;
is null


But when a null value is in a sequence of values (like sum of a null-able column), the calculated value of null value will be 0


select sum(value) from
(
select case when mod(level , 2) = 0 then null else level end as value from dual
connect by level <= 10
)
is 25


This will be more interesting when seeing the result of


select (1 + null) from dual
is null


As any operation with null will result null (except
is null
operator).


==========================

Some update due to comments:


create table odd_table as select sum(null) as some_name from dual;


Will result:


create table ODD_TABLE
(
some_name NUMBER
)


some_name NUMBER => ???

Answer

SQL does not treat NULL values as zeros when calculating SUM, it ignores them:

Returns the sum of all the values, or only the DISTINCT values, 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.

Comments