I need Total return NULL only If all values are NULL

user_2362035 0
Viewed 480

A query like these 

      SELECT A,B,C,D, (A+B+C+D) as TOTAL
      FROM TABLES

If A,B,C and D is NULL. i need to return NULL. But if any one of the them is not NULL. Other will change from NULL to zero. And total(a+b+c+d).

Now try this way

      SELECT A,B,.. CASE WHEN (A IS NULL) AND (B IS NULL) AND ... THEN NULL
                    ELSE ISNULL(A,0) + ISNULL(B,NULL) +... END

But it is so long and I have a lot of total in this whole query. What the best way I can use for this problem?

tsql sql-server sql
3 Answers

The semantics you want are the same as those provided by SUM.

SELECT A,B,C,D,
       (SELECT SUM(val)
        FROM   (VALUES(A),
                      (B),
                      (C),
                      (D)) T (val)) AS Total
FROM   YourTable
user_732260

I would use COALESCE function.

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

SELECT
    CASE WHEN COALESCE(A,B,C,D) IS NOT NULL THEN
       COALESCE(A,0 ) + COALESCE(B,0 )+ COALESCE(C,0 ) + COALESCE(D,0 ) 
    END 
FROM TABLES
user_51760710

COALESCE() is a function that you can use:

SELECT A,B,.. 
  CASE WHEN COALESCE(A,B,C,D) IS NULL THEN NULL ELSE ISNULL(A,0) + ISNULL(B,0) +... END
user_27039280
Related