Why warn about NULLs in COUNT(col)?

Question

This is a bit of a philosophical question, I guess...

Say I execute this query in SQL Server:

SELECT mygroup, COUNT(mycolumn)
FROM mytable
GROUP BY mygroup

The counted column contains NULLs and the ANSI_WARNINGS option is ON, so I get:

Warning: Null value is eliminated by an aggregate or other SET operation.

I understand what this means. And there are lots of ways to 'fix' this warning. But... why is it complaining in the first place?

Skipping any NULLs is all COUNT(mycolumn) does! If I wanted to count all rows, including NULLs for this column, I would have used COUNT(*). Isn't it clear that it's my intention to skip NULLs?

I guess the ANSI standard demands this warning, even in this obvious case. Why?!

Machinist

Answer 1

The warning only appears when an actual null is present in the aggregate resultset. I believe it is there to remind you that nulls will not be counted and to warn you should you come across data consistency issues because a null appears where you did not expect.

Lala

I expect NULLs, or I would have used COUNT(*). – Machinist

I understand that you expect nulls, but remember ansi_warnings is enabled by default. With this enabled, you will get warnings. Also just because you know the difference between count(column) and count(*) there are people out there who do not understand the difference. It is beneficial to these people to see a warning that there could be data inconsistencies or inaccurate aggregations. If you do not want this setting, you can run "set ansi_warnings off". – Lala

ANSI_WARNINGS are fine for MIN, MAX, etc. But for COUNT it's confusing IMHO. – Machinist