CODEWITHSUNDEEP
sql-server
Dan
Dan

Reputation: 693

How to make a SQL Server SUM return null if the summed values are all null

I have a table with a column numeric. The column can have null values. I need to SUM all values but if all values are NULL, it should return NULL, but if at least one value is not null, it should return the sum.

Is there such a function? Select Func?(Column) From Table?

Upvotes: 0

Views: 256

Answers (1)

Thom A
Thom A

Reputation: 95564

The SUM of NULL is NULL:

WITH VTE AS (
    SELECT CONVERT(int,C1) AS C1, C2, C3
    FROM (VALUES(NULL, NULL, 0),
                (NULL, 0, 0)) V (C1, C2, C3))
SELECT SUM(C1),
       SUM(C2),
       SUM(C3)
FROM VTE;

This returns the values NULL, 0, and 0, respectively.

Upvotes: 1

Related Questions