This question already has an answer here:

For the following query

SELECT sum(c) 
FROM (SELECT 1 c WHERE false) t;

I thought it takes the sum of zero numbers, and therefore should return 0. (Similarly, I expect, e.g. an array aggregate function to return an empty array on zero elements).

Yet, it is returning a NULL (using PostgreSQL 11).

=> SELECT sum(c) FROM (SELECT 1 c WHERE false) t; 

(1 row)

This doesn't make sense to me logically.

Why is the sum of zero numbers (for any numeric type) not zero? Is this NULL returning behavior standard?

Or maybe I am missing something. Any reason one has to make the sum NULL in some corner cases?

marked as duplicate by mustaccio, Josh Darnell, Evan Carroll, Paul White May 15 at 4:01

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.


This is a requirement from the SQL standard. I'm most familiar with the 1992 standard. Quoting from it, with emphasis mine:

General Rules

1) Case:

a) If COUNT(*) is specified, then the result is the cardinality of T.


b) Otherwise, let TX be the single-column table that is the result of applying the to each row of T and eliminating null values. If one or more null values are eliminated, then a completion condition is raised: warning- null value eliminated in set function.

2) If DISTINCT is specified, then let TXA be the result of elimi-
nating redundant duplicate values from TX. Otherwise, let TXA be


a) If the COUNT is specified, then the result is the cardinality of TXA.

b) If AVG, MAX, MIN, or SUM is specified, then


i) If TXA is empty, then the result is the null value.

In short, NULLs are always excluded from the result of SUM, MAX, MIN, and AVG. I can't tell you exactly why this is, other than I think it's useful to be able to distinguish between a sum of a single value of 0 and a sum of an empty set.

Not the answer you're looking for? Browse other questions tagged or ask your own question.