I caught this interesting item over on Pinal Dave’s blog: Eleven Interview Questions that Look Too Easy. I decided to give you a few thoughts from me on the SUM one.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
The Sum of Nothing
I am guessing more people working with SQL know that if you have a NULL in your data and try to sum the column, you get the NULL ignore. After all, you can’t sum up values if one is unknown. The code and query below show this:
Note, with ANSI_WARNINGS I get a note in the Messages tab:
However, what if there are no rows? Would you expect a 0, because if there isn’t any data, the sum is zero, correct?
No.
Why? The docs don’t mention this (I’ve added a PR). The ANSI standard notes that if all values are NULL or the set is empty, NULL is returned.
Most of us don’t query empty tables, but we could get an empty set. Remember, the column list, and therefore aggregate, is evaluated after the WHERE and JOIN clauses. Therefore, as you see below, I could get a NULL in a sum where I expect data.
Make sure you account for this in your queries.
SQL New Blogger
I read reading another blog (Pinal’s) and realized this was interesting. I thought about if I’d have a problem and realized that I could because I’ve often assumed there is some data, but if I let users filter data from an app, I could return NULL.
Easy to write, about 15 minutes to setup and do. You could do this.
Leave a comment