SUM function to return null if null value exists

Question

I have this query

SELECT (SELECT cost FROM table1 t2 WHERE t2.edate=>table1.edate) AS cost
FROM table1
WHERE table1.orderNo = 'CS119484568'

the above query returns:

cost
4
3
null
null
5

I want to get sum of all or null if any row have a null

SELECT SUM((SELECT cost FROM table1 t2 WHERE t2.edate=>table1.edate)) AS cost
FROM table1
WHERE table1.orderNo = 'CS119484568'

Expected result NULL in the above case.

Possible duplicate of [MySQL - Sum only if all rows are not null, else return null](https://stackoverflow.com/questions/22398437/mysql-sum-only-if-all-rows-are-not-null-else-return-null) — sticky bit, Aug 07 '18 at 22:43
I'm sorry, could you explain in more detail? Your examples don't make much sense. In the first one, the subquery will most likely return more than 1 row, so that can't be correct. Can you maybe give a sample of the data in `table1`? — Vilx-, Aug 07 '18 at 23:30

score 0 · Answer 1 · answered Aug 07 '18 at 22:45

0

Here's my attempt:

SELECT
    (SELECT
        case when count(*)=count(cost) then sum(cost) else null end
     FROM table1 t2
     WHERE t2.edate=>table1.edate
    ) AS cost
FROM table1
WHERE table1.orderNo = 'CS119484568'

This is based on the fact that count() only counts non-null values.

answered Aug 07 '18 at 22:45

Vilx-

104,512
87
279
422

It returned same result as my 1st query. The subquery is meant to return `cost` and those cost would have nulls as well. The operation needs to be done on the resultset of subquery, not inside subquery. I hope i have explain clearly. – Abdul Rehman Aug 07 '18 at 23:01

score 0 · Answer 2 · answered Aug 07 '18 at 22:47

0

You can use if for this:

SELECT IF((SELECT COUNT(*) FROM table1 t2 WHERE t2.edate=>table1.edate AND cost IS NULL), null, (SELECT SUM(cost) FROM table1 t2 WHERE t2.edate=>table1.edate)) AS cost FROM table1 WHERE table1.orderNo = 'CS119484568'

answered Aug 07 '18 at 22:47

Sookie Singh

1,543
11
17

Two subquries as you mentioned could maybe solve the problem, but I was trying to avoid it. Will look into it later. Thanks – Abdul Rehman Aug 07 '18 at 23:03

score 0 · Answer 3 · answered Aug 07 '18 at 22:51

I think I would phrase your query as:

SELECT SUM(CASE WHEN t1.edate >= tt1.edate THEN cost END)
FROM table1 t1 CROSS JOIN
     (SELECT edate
      FROM table1
      WHERE t1.orderNo = 'CS119484568'
     ) tt1;

Then I would use a CASE expression for your result:

SELECT (CASE WHEN SUM(t1.edate >= tt1.edate AND cost IS NOT NULL) = 0  -- no nulls
             THEN SUM(CASE WHEN t1.edate >= tt1.edate THEN cost END)
        END)
FROM table1 t1 CROSS JOIN
     (SELECT edate
      FROM table1
      WHERE t1.orderNo = 'CS119484568'
     ) tt1;

SUM function to return null if null value exists

3 Answers3