CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
milan
milan

Reputation: 2515

aggregate of an empty result set

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))?

Upvotes: 12

Views: 17471

Answers (4)

Asad Nauman
Asad Nauman

Reputation: 969

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 external NVL avoids the cases when there is no row in the result set. Inner NVL is used for null column values, consider the case of (1 + null) and it will result in null. So inner NVL is also necessary other wise in alternate set default value 0 to the column.

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

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;

Upvotes: 6

Andriy M
Andriy M

Reputation: 77657

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:

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

Upvotes: 15

Michael Buen
Michael Buen

Reputation: 39393

Just do this:

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

By the way, COALESCE inside of SUM is redundant, even if capacity is NULL, it won't make the summary null.

To wit:

create table objects
(
    capacity int null
);

insert into objects(capacity) values (1),(2),(NULL),(3);

select sum(capacity) from objects;

That will return a value of 6, not null.

And a coalesce inside an aggregate function is a performance killer too, as your RDBMS engine cannot just rip through all the rows, it has to evaluate each row's column if its value is null. I've seen a bit OCD query where all the aggregate queries has a coalesce inside, I think the original dev has a symptom of Cargo Cult Programming, the query is way very sloooowww. I removed the coalesce inside of SUM, then the query become fast.

Upvotes: 1

Related Questions