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.

Gurwinder Singh · Accepted Answer · 2017-02-27 07:29:04Z

3

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;

edited Feb 27, 2017 at 7:29

answered Feb 27, 2017 at 6:52

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abhishek Tiwari · Accepted Answer · 2017-02-27 06:47:45Z

0

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

You can run this script

answered Feb 27, 2017 at 6:47

Abhishek Tiwari

334 bronze badges

3 Comments

3BK Over a year ago

I think you mean WHERE Price IS NOT NULL AND Quant IS NOT NULL

Abhishek Tiwari Over a year ago

Yes i mean this

Thorsten Kettner Over a year ago

Well, you could have updated your answer when you were told of your mistake. Anyway, your query gets the same result as OP's query. Seems you haven't understood the request.

3BK · Accepted Answer · 2017-02-27 06:54:31Z

0

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
;

answered Feb 27, 2017 at 6:54

3BK

1,3581 gold badge8 silver badges11 bronze badges

Comments

Vijunav Vastivch · Accepted Answer · 2017-02-27 07:06:23Z

0

How about this way:

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

answered Feb 27, 2017 at 7:06

Vijunav Vastivch

4,2111 gold badge18 silver badges32 bronze badges

Comments

Chris Miller · Accepted Answer · 2017-02-27 16:30:57Z

0

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".

answered Feb 27, 2017 at 16:30

Chris Miller

4,9094 gold badges35 silver badges50 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related