Mohsen Heydari Mohsen Heydari - 2 years ago 138
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


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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download