CODEWITHSUNDEEP
sqlsql-server-2008
Matthew
Matthew

Reputation: 10444

Why are my SQL SUMs not coming back NULL when they include NULL values?

I use a CTE to calculate spans of time in a log as shown in this fiddle:

http://www.sqlfiddle.com/#!3/b99448/6

Note that one of the rows has a NULL value because that is the most recent log entry and no calculation could be made.

However, if I SUM these results the NULL is being treated as a zero:

http://www.sqlfiddle.com/#!3/b99448/4

How can I get this to stop ignoring NULL values? I would expect the sum to be NULL since it is adding a NULL value.

Upvotes: 3

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

The aggregation functions ignore NULL values. They are not treated as 0 -- the distinction is more important for AVG(), MIN(), and MAX(). So, SUM() only returns NULL when all values are NULL.

If you want to get NULL back, here is a simple expression:

select (case when count(*) = count(a.DateTimeChangedUtc) and
                  count(*) = count(b.DateTimeChangedUTC)
             then SUM(DATEDIFF(SECOND, a.DateTimeChangedUtc, b.DateTimeChangedUTC)) 
        end) AS TimeSpentSeconds

This returns NULL if either argument is ever NULL.

Upvotes: 5

Related Questions