Why does SELECTing sum() return null instead of 0 when there are no matching records?

Question

This is but one of countless little details that make me frustrated all the time, forces me to memorize all kinds of special code and causes my queries to become uglier than they should have to be.

Try this query on for size:

SELECT sum(amount) FROM table WHERE conditions;

If it finds no records to sum() the amount of, it returns empty/null/undefined instead of 0, causing the output to be unexpectedly empty in my application, instead of the "sum" of zero. But sum() means "the sum", so why not just return 0?

I am aware of the solution. You "simply" do:

SELECT COALESCE(sum(amount), 0) FROM table WHERE conditions;

Now it will return 0 even if there are no records. But it's ugly and it no longer feels "fun" to use. Not that databases are supposed to be a "fun game", but you know what I mean: if a query becomes too convoluted/"ugly", it no longer feels satisfying to use it, especially if you know that this will have to be repeated in all kinds of places and it's not just some obscure, one-off edge case.

What was the thought process behind making it behave like this? I have many other issues related to null, but I'll focus on this one thing for this question.

Answer 1

This is specified by the SQL standard in section 4.16.4 Aggregate functions: :

If no row qualifies, then the result of COUNT is 0 (zero), and the result of any other aggregate function is the null value.

Answer 2

Note that it happens only if all values are NULL or has no values/entries at all.

You can (and should) always test what happens behind the curtains.

Example with custom values:

WITH test_data (a, b) as (
  SELECT * 
  FROM (VALUES 
         ('example1', 1), 
         ('example2', 2), 
         ('example3', NULL), 
         ('example3', 3), 
         (NULL, NULL), 
         (NULL, 5), 
         (NULL, 5),
         ('example4', NULL)
       ) t
)
SELECT 
  a,
  SUM(b) AS b
FROM test_data
GROUP BY 1

Answer 3

If it finds no records to sum() the amount of, it returns empty/null/undefined instead of 0, causing the output to be unexpectedly empty in my application, instead of the "sum" of zero. But sum() means "the sum", so why not just return 0?

Simply because:

NULL  !=  0 

NULL is not any kind of default value, it is not any kind of known value.
It is the deliberate means of recording the absence of any, meaningful value.

The sum of NULLs is NULL.
The result of any aggregate function on NULLs is NULL.
The result of any operation at all on NULLs is NULL.

Here's a "fun" one:
NULL is not "equal" to anything else, not even NULL itself!

NULL is NULL    True
NULL =  NULL    False
NULL != NULL    False

Answer 4

From postgre 7.1 you can create custom aggregates

create or replace function sum0_iteration(state int, value int) 
returns int language sql
as 'select state + coalesce(value, 0)';

create or replace aggregate sum0(int) (
    stype = int,
    sfunc = 'sum0_iteration',
    initcond = 0
);

Of course, you'll have to overload this functions for different argument type, not just int, but user queries becomes a little better ))

select sum(1), sum0(1) where false

sum|sum0|
---+----+
   |   0|

Answer 5

My solution for this problem is:

SELECT IFNULL(SUM(amount), 0) FROM table WHERE conditions;

I think it's more clearer and easier to read and understand. Nevertheless it would be a nice config setting aka NULL_SUM_TO_ZERO ...