Set of empty result set

Question

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

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

Result:

 sum ----- (1 row) 

Subquery: Does this work in Oracle using SUM(NVL(capacity, 0)) ?

Answer 1

From the aggregated function documentation page :

It should be noted that, in addition to count these functions return a null value if none of the lines are selected . In particular, sum does not return a single row zero, not zero, as you might expect. The coalesce function can be used to replace zero with a zero value.

So, if you want to guarantee the return value, apply coalesce to the result of sum , and not to its argument:

 SELECT COALESCE(SUM(capacity), 0) … 

As for the Oracle "subquery", well, I could not find any NULL concept on the official page of the document ( for 10.2 , in particular), but two other sources are unambiguous:

• Oracle SQL Features :

  SUM([DISTINCT] n) The sum of n, ignoring NULL

• sum of aggregate function [Oracle SQL] :

  ... if sum () is created over some numbers, zeros are ignored, as shown in the following example ...

That is, you do not need to apply NVL to capacity . (But, as with coalesce in PostgreSQL, you can apply it to sum .)

Answer 2

The fact is that an aggregate always returns a row, even if no rows were aggregated (as in the case of your query). You have summed an expression without strings. Therefore, the null value you get.

Try this instead:

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

Answer 3

Just do the following:

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

By the way, COALESCE inside the SUM is redundant, even if the capacity is NULL, it will not make the total value null.

In particular:

 create table objects ( capacity int null ); insert into objects(capacity) values (1),(2),(NULL),(3); select sum(capacity) from objects; 

This will return a value of 6, not null.

And sharing within an aggregate function is also a killer of performance, since your RDBMS engine cannot just iterate over all rows, it must evaluate each column of rows if its value is null. I saw a little OCD request where all aggregated requests have coalescence inside, I think that the original developer has the Cargo Cult Programming symptom, the request is very stupid. I removed the coalesce inside the SUM, then the request quickly.

Answer 4

Although this post is very old, but I would like to update what I use in such cases

 SELECT NVL(SUM(NVL(capacity, 0)),0) FROM objects WHERE false; 

Here, the external NVL avoids cases where there is no row in the result set. The internal NVL is used for null column values, consider the case of (1 + null), and this will result in a null value. Thus, the internal NVL also needs another wise one in the alternative default value of 0 for the column.