Why does SELECTing sum() return null instead of 0 when there are no matching records?
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.
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.