SQL - Get Sum of fields if all fields are not null

Question

I'm trying to perform a SUM function, but I only want it to return a value if all the fields are not null. Right now when it performs the SUM, it skips over NULL values and just adds up the non-nulls. What I would like to happen is, if even one value is null in the sum, it'll just return null. Here is a sample table:

  Table_1
  -------------
  Price   Quant
  10.00 |  NULL
  11.00 |  100 

Here is the query I'm running:

SELECT SUM((Price * Quant)) FROM [Table_1]

At this point, it returns a value of 1100. I'd like it to return NULL instead since the first record's Price * Quant is null.

Answer 1

You can do something like:

Select 
  case when count(
    case when price + quant is null then 1 end
  ) = 0 then sum(price*quant) end as val
From your_table;

We are checking price + quant for null because null + anything is null, if either price or quant is null, it will return null. You can of course use other arithmetic operation, but I think sum is lightest one.

You can also do separate checks:

Select 
  case when count(
    case when price is null or quant is null then 1 end
  ) = 0 then sum(price*quant) end as val
From your_table;

Answer 2

    SELECT SUM((Price * Quant)) FROM [Table_1]
 WHERE Price IS NOT NULL WHERE Quant IS NOT NULL

You can run this script

Answer 3

I would do it like this...

SELECT
    CASE
        WHEN MAX(T2.NoValue) IS NOT NULL
            THEN NULL
        ELSE SUM(Price * Quant)
    END AS Total
FROM Table_1 T1
LEFT JOIN
(
    SELECT TOP 1 1 AS NoValue
    FROM Table_1 
    WHERE Quant IS NULL
    OR Price IS NULL
) T2
    ON NoValue = 1
;

Answer 4

How about this way:

SELECT case when Quant is null then null else SUM((Price * Quant)) as this_sum end FROM [Table_1]

Answer 5

If you want to test for nullness of both Price and Quant, you can use the following:

-- Sample T-SQL schema to demonstrate the SQL query
DECLARE @tb TABLE
(
    Price INT NULL,
    Quant INT NULL
)

INSERT INTO @tb(price, quant) VALUES (10, null)
INSERT INTO @tb(price, quant) VALUES (20, 100)

IF EXISTS(SELECT 1 FROM @tb WHERE (price + quant) IS NULL)
    SELECT NULL AS C
ELSE
    SELECT SUM(price * quant) AS C
    FROM @tb

If you just need to test for quant, then replace "(price + quant)" with use "quant".