Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
I can see where all the doubt in this question arises - as discussed. Only saving grace is the statement in the Aggregate Function link, which states:
Except for COUNT, aggregate functions ignore null values.
However even reading the information in the link for COUNT, you get the following:
COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.
COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.
COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.
My guess the problem then is that for all other aggregate functions, it isn't possible to ignore null at all, however with COUNT you have an option based on the execution of the query. Most of the remaining aggregate functions cant be used generically (with *) and so by design have to either include or exclude nulls. The default then is to ignore them.
Nice question, but I'd argue GROUPING doesn't ignore NULL values as well.
That's what I took from BOL and got the question wrong.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. —Charles Babbage, Passages from the Life of a Philosopher
I also clicked COUNT right away, then paused and thought about GROUPING. I actually looked it up and saw that I didn't ignore NULLs. Guessing between the two correct answers, I chose poorly. It's not a big deal, just debatable.