SQL Server – How to Correct Aggregate Functions Ignoring NULLs

aggregatenullsql server

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.

Best Answer

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;

Related Solutions

SQL Server – Query to Find GUIDs ‘Not In’ Null Containing Subquery

Just to provide some additional explanation to billinkc's answer.

If null is a trump card you might be wondering why doesn't WHERE 2 IN (2,3, NULL) exhibit the same behavior?

That one works as expected because it evaluates to (2=2) OR (2=3) OR (2=NULL).

Under the rules of three valued logic for Or-ed conditions if any of them evaluate to true the expression is true. Otherwise if any of them evaluate to unknown the expression is unknown. The only other possibility is that all are false in which case the expression evaluates to false.

In order for a row to be returned in SQL the WHERE clause must evaluate to true rather than false or unknown. The above does that.

The expression 1 NOT IN (2,3, NULL) evaluates to (1 <> 2) AND (1 <> 3) AND (1 <> NULL). When conditions are AND-ed all of them must evaluate to true in order for the expression to evaluate to true.

The presence of the NULL in the list guarantees that there will be at least one UNKNOWN and that this will never be the case. Hence the reason for the " NULL pooches it all" behaviour in this context.

To give an analogy here as to why the NOT IN behaviour makes sense.

Three friends Tom, Dick, and Harry are sitting in a railway carriage with a complete stranger whose name is unknown to them.

If Tom is asked "Is your name different from everyone else's in this carriage?" then it is impossible for him to answer with any certainty.

Even though he knows that Tom <> Dick and Tom <> Harry (so the statement might be true) the veracity of the statement overall hinges on the stranger's name and this is not known.

This is analogous to the SQL 'Tom' NOT IN ('Dick', 'Harry', Null)

Oracle 11g – Aggregate Function and Query

Simple, but "dirty":

with t as (
  select 1 as id, 'Y' as received from dual union
  select 1 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'Y' as received from dual union
  select 3 as id, 'N' as received from dual union
  select 3 as id, 'Y' as received from dual 
)
select min(max(received)) from t group by id;

M
-
Y

Change input (e.g. change id to 4 in last line):

with t as (
  select 1 as id, 'Y' as received from dual union
  select 1 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'Y' as received from dual union
  select 3 as id, 'N' as received from dual union
  select 4 as id, 'Y' as received from dual 
)
select min(max(received)) from t group by id;

M
-
N

Best Answer

Related Solutions

SQL Server – Query to Find GUIDs ‘Not In’ Null Containing Subquery

Oracle 11g – Aggregate Function and Query

Related Question