aggregate of an empty result set

Question

I would like the aggregates of an empty result set to be 0. I have tried the following:

SELECT SUM(COALESCE(capacity, 0))
  FROM objects
 WHERE null IS NOT NULL;

Result:

sum 
-----

(1 row)

Subquestion: wouldn't the above work in Oracle, using SUM(NVL(capacity, 0))?

Answer 1

The thing is, the aggregate always returns a row, even if no rows were aggregated (as is the case in your query). You summed an expression over no rows. Hence the null value you're getting.

Try this instead:

select coalesce(sum(capacity),0)
from objects
where false;

Answer 2

From the documentation page about aggregate functions:

It should be noted that except for count, these functions return a null value when no rows are selected. In particular, sum of no rows returns null, not zero as one might expect. The coalesce function may be used to substitute zero for null when necessary.

So, if you want to guarantee a value returned, apply COALESCE to the result of SUM, not to its argument:

SELECT COALESCE(SUM(capacity), 0) …

As for the Oracle 'subquestion', well, I couldn't find any notion of NULLs at the official doc page (the one for 10.2, in particular), but two other sources are unambiguous:

• Oracle SQL Functions:

  SUM([DISTINCT] n) Sum of values of n, ignoring NULLs

• sum aggregate function [Oracle SQL]:

  …if a sum() is created over some numbers, nulls are disregarded, as the following example shows…

That is, you needn't apply NVL to capacity. (But, like with COALESCE in PostgreSQL, you might want to apply it to SUM.)