Thanks. It appears I need to stop overusing coalesce. I was told that sql NULL means "A value that is not yet known", which nicely explains why 1+NULL, 1 < NULL, 1 > NULL, 1 = NULL is always NULL. Now I know that AVG(test_scores) produces the average of the known values automatically.

- - -

I just did a test, and it appears the COALESCE is needed in this case. Running an aggregate where all values are null, results in NULL (the empty department). You need to do something because the total salary of an empty department is known to be zero.

> Aggregates generally do the right thing with null without the coalesce.

Aggregates generally do the most-likely-to-be-right thing with NULL values if there is at least one non-null input to the aggregate. The thing is, if you depend on this, you'll run into real data situations where all the inputs are NULL, the result is NULL, and that's not what you expected.

If you are aggregating over an expression that can be NULL, and you always want a non-NULL answer, you probably need to use coalesce or something similar so that you don't have non-NULL inputs to the aggregate.