2

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.

9
  • 9
    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) Commented Feb 19, 2015 at 14:44
  • 6
    In other words, it already works correctly. Problem solved.
    – Andriy M
    Commented Feb 19, 2015 at 14:46
  • 2
    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. Commented Feb 19, 2015 at 14:55
  • 6
    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" Commented Feb 19, 2015 at 15:00
  • 4
    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.
    – Andriy M
    Commented Feb 19, 2015 at 15:00

1 Answer 1

9

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;
2
  • 2
    Seems a little overkill. You could just compare COUNT(col) and COUNT(*).
    – Andriy M
    Commented Feb 19, 2015 at 14:55
  • @AndriyM: good point, I added that as well. Thanks
    – user1822
    Commented Feb 19, 2015 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.