Does sum override null values?

1

Greetings

The older I get the wiser I do not get, especially the longer I am way from sql server.

;with darkforce --so as to sample your data
as
(
select GETUTCDATE() as TimeDate, 193 as EIPCount, null as Prog1, 6 Prog2 union all
select dateadd(minute, -30, GETUTCDATE()) as TimeDate, 194 as EIPCount, 8 as Prog1, 6 Prog2 union all
select dateadd(minute, -60, GETUTCDATE()) as TimeDate, 192 as EIPCount, 8 as Prog1, 6 Prog2 union all
select dateadd(minute, -90, GETUTCDATE()) as TimeDate, 194 as EIPCount, 8 as Prog1, 6 Prog2

)
select *, (SELECT sum(v) 
            from (VALUES (Prog1), (Prog2)) AS value(v)
		  ) as ByteCount
from darkforce i

the SUM aggregator seems to brute force the null values to comply in the summation. How can I figure out that one of the columns is null which I want to discard?

Thanks

2

from docu

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

3

Did you mean that you want to get bytecount = null if one of the values is null? You could use something like this (and I would be the first one to admit that it is not the prettiest).

select *, (SELECT SUM(v) * CASE WHEN COUNT(v) <> SUM(1) THEN NULL ELSE 1 END 
            from (VALUES (Prog1), (Prog2)) AS value(v)
		  ) as ByteCount
from darkforce i
1 Like
4

so I want to filter out any rows that have any of the one column in that row that is null

as you can see the 3rd row show a value of 14 when it should be null also

image

THanks

5

Maybe just add another query level in your derived query:
...

select *, (select case when nullcount > 0 then null else ByteCount end as bytecount
           from (SELECT sum(v) as ByteCount, sum(case when v is null then 1 else 0 end) as nullcount
            from (VALUES (Prog1), (Prog2)) AS value(v)
		  ) as derived) as ByteCount

...

1 Like
6

Sorry @JamesK this works!! I had excluded Prog 3 :frowning:

7

this also works! thanks @ScottPletcher !!!