SQL Server's aggregate functions ignore nulls. How do I correct this?

Question

According to MS's knowledgebase,

Except for COUNT, aggregate functions ignore null values.

Does anyone know how I can get it to work correctly, that is, return a null if it attempts to aggregate with a null in the values?

Example:

SELECT SUM("col")
FROM (
 SELECT NULL "col"
 UNION
 SELECT 1 "col"
) test

I would expect this query to return NULL, not 1.

• ypercubeᵀᴹ: This is a behaviour defined in the SQL standard. If you want a different behaviour, write the appropriate query - say with WHERE EXISTS (SELECT ... WHERE column iS NULL)
• Andriy M: In other words, it already works correctly. Problem solved.
• Aaron Bertrand: Note that the behavior is arguably different depending on the aggregate. Replace SUM with COUNT to see what I mean. In fact I would argue that COUNT ignores NULL as well.
• ypercubeᵀᴹ: I don't know. Perhaps because you wrote "how I can get it to work correctly" while you meant *"how I can get it to work the way I want it"
• Andriy M: Your saying "how I can get it to work correctly" seems to imply that the documented behaviour is not correct. So, perhaps someone disagreed with that premise, considering such a question not useful.

Answer 1

You can simulate this by applying a where clause to your query:

with test_data (col) as (
 select null union all 
 select 1 union all
 select 2
)
select sum(col)
from test_data
where not exists (select 1 
 from test_data
 where col is null);

Edit

Andriy is right, this can also be written as:

with test_data (col) as (
 select null union all 
 select 1 union all
 select 2
)
select case 
 when count(*) = count(col) then sum(col)
 else null
 end
from test_data;

• Andriy M: Seems a little overkill. You could just compare COUNT(col) and COUNT(*).
• user1822: @AndriyM: good point, I added that as well. Thanks