CODEWITHSUNDEEP
sqlsql-servert-sql
Fame th
Fame th

Reputation: 1038

I need Total return NULL only If all values are NULL

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?

Upvotes: 2

Views: 844

Answers (3)

Amin Mozhgani
Amin Mozhgani

Reputation: 604

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

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453067

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

Upvotes: 5

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 5

Related Questions