How to return null when summing values that include nulls

[cricketbird]

Currently, if my data in a given column is: (1, 2, 3, 4, 5, (missing), 7, 8, 9), then when I sum these I get 39, whereas I want to get NULL because in my business case I can't sum these values if I don't have all these values. I don't want to return a partial sum.

So, in a simple query, I'd have:

SELECT Sum([MyColumn]) AS [SumOfMyColumn]
FROM MyTable;

How do I get it to return NULL if there is missing data?

(if anyone uses R, I'm trying to do: sum(x, na.rm = FALSE)

 

[plog]

This is going to take a subquery to determine if there are any nulls:

SELECT Min(IsNull([MyColumn])) AS HasNulls, Sum(MyColumn) AS ColumnSum
FROM YourTable;

Then make a query on that and use HasNulls to determine what to return:

SELECT IIF(HasNulls=False, ColumnSum) AS YourSum
FROM ThatQuery

 

[cricketbird]

This is going to take a subquery to determine if there are any nulls:

SELECT Min(IsNull([MyColumn])) AS HasNulls, Sum(MyColumn) AS ColumnSum
FROM YourTable;

Then make a query on that and use HasNulls to determine what to return:

SELECT IIF(HasNulls=False, ColumnSum) AS YourSum
FROM ThatQuery

Thanks! It makes sense - I will give it a whirl in the morning

 

[MarkK]

You can also do this...

SELECT IIf(Sum([MyColumn] Is Null), Null, Sum([MyColumn])) AS MyColumnSum
FROM tMyTable;

If one or more rows have nulls, this expression: Sum([MyColumn] Is Null) is true, so return a Null, otherwise return the complete sum.

 

[cricketbird]

You can also do this...

SELECT IIf(Sum([MyColumn] Is Null), Null, Sum([MyColumn])) AS MyColumnSum
FROM tMyTable;

If one or more rows have nulls, this expression: Sum([MyColumn] Is Null) is true, so return a Null, otherwise return the complete sum.

I wanted this to work, and it seems like it should, but I still get values and not NULLs as the sum when I do this. I'll keep playing around with this...

 

[MarkK]

If you want more help, show your exact code/SQL etc...

Remember, if in a particular row a field like [MyColumn] is null, then the boolean expression "[MyColumn] Is Null" evaluates to -1 (True) for that row. If we then Sum() that expression, we get a (negated) count of null rows.

The bracketing and parenthesis make a HUGE difference. This, for instance, will fail...

SELECT IIF(Sum([MyColumn]) Is Null, Null, Sum([MyColumn]))

This will work...

SELECT IIF(Sum([MyColumn] Is Null), Null, Sum([MyColumn]))

 

[cheekybuddha]

You can try this variation:

SELECT
  IIf(
    DCount('*', 'tMyTable', [MyColumn] Is Null) > 0,
    Null,
    Sum([MyColumn]
  ) AS MyColumnSum
FROM tMyTable;