Why SUM(null) is not 0 in Oracle?

Question

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
)

Why some_name column is of type number?

Answer 1

If you are looking for a rationale for this behaviour, then it is to be found in the ANSI SQL standards which dictate that aggregate operators ignore NULL values.

If you wanted to override that behaviour then you're free to:

Sum(Coalesce(<expression>,0))

... although it would make more sense with Sum() to ...

Coalesce(Sum(<expression>),0)

You might more meaningfully:

Avg(Coalesce(<expression>,0))

... or ...

Min(Coalesce(<expression,0))

Other ANSI aggregation quirks:

1. Count() never returns null (or negative, of course)
2. Selecting only aggregation functions without a Group By will always return a single row, even if there is no data from which to select.

So ...

Coalesce(Count(<expression>),0)

... is a waste of a good coalesce.

Answer 2

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.

Answer 3

You're looking at this the wrong way around. SUM() operates on a column, and ignores nulls.

To quote from the documentation:

This function takes as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.

A NULL has no data-type, and so your first example must return null; as a NULL is not numeric.

Your second example sums the numeric values in the column. The sum of 0 + null + 1 + 2 is 3; the NULL simply means that a number does not exist here.

Your third example is not an operation on a column; remove the SUM() and the answer will be the same as nothingness + 1 is still nothingness. You can't cast a NULL to an empty number as you can with a string as there's no such thing as an empty number. It either exists or it doesn't.

Answer 4

Arithmetic aggregate functions ignore nulls.

• SUM() ignores them
• AVG() calculates the average as if the null rows didn't exist (nulls don't count in the total or the divisor)

Answer 5

As Bohemian has pointed out, both SUM and AVG exclude entries with NULL in them. Those entries do not go into the aggregate. If AVG treated NULL entries as zero, it would bias the result towards zero.

It may appear to the casual observer as though SUM is treating NULL entries as zero. It's really excluding them. If all the entries are excluded, the result is no value at all, which is NULL. Your example illustrates this.

Answer 6

This is incorrect: The sum of 0 + null + 1 + 2 is 3; select 0 + null + 1 + 2 total from dual;

Result is null! Similar statements give result null if any operand is null.

Answer 7

Here's a solution if you want to sum and NOT ignore nulls.

This solution splits the records into two groups: nulls and non-nulls. NVL2(a, 1, NULL) does this by changing all the non-nulls to 1 so they sort together identically. It then sorts those two groups to put the null group first (if there is one), then sums just the first of the two groups. If there are no nulls, there will be no null group, so that first group will contain all the rows. If, instead, there is at least one null, then that first group will only contain those nulls, and the sum of those nulls will be null.

SELECT SUM(a) AS standards_compliant_sum,
       SUM(a) KEEP(DENSE_RANK FIRST ORDER BY NVL2(a, 1, NULL) DESC) AS sum_with_nulls
  FROM (SELECT 41   AS a FROM DUAL UNION ALL
        SELECT NULL AS a FROM DUAL UNION ALL
        SELECT 42   AS a FROM DUAL UNION ALL
        SELECT 43   AS a FROM DUAL);

You can optionally include NULLS FIRST to make it a little more clear about what's going on. If you're intentionally ordering for the sake of moving nulls around, I always recommend this for code clarity.

SELECT SUM(a) AS standards_compliant_sum,
       SUM(a) KEEP(DENSE_RANK FIRST ORDER BY NVL2(a, 1, NULL) DESC NULLS FIRST) AS sum_with_nulls
  FROM (SELECT 41   AS a FROM DUAL UNION ALL
        SELECT NULL AS a FROM DUAL UNION ALL
        SELECT 42   AS a FROM DUAL UNION ALL
        SELECT 43   AS a FROM DUAL);