Why Using COALESCE or CASE keep returning null-CodePudding

I have the following SQL Query :

(SELECT ROUND(SUM(NBTOSUM)/1000000,1) FROM MyTable t2 WHERE t2.ELEMNAME IN ('A','B','C'))

Which works fine.

But Where there is no 'A','B','C' the result of the select is (null)

So to handle it, I did the following :

(SELECT COALESCE(ROUND(SUM(NBTOSUM)/1000000,1),0) FROM MyTable t2 WHERE t2.ELEMNAME IN ('A','B','C'))

And also try :

(SELECT 
 CASE
        WHEN SUM(NBTOSUM)/1000000 IS NULL THEN 0
        ELSE ROUND(SUM(NBTOSUM)/1000000,1)
END 
FROM MyTable t2 WHERE t2.ELEMNAME IN ('A','B','C'))

But both keep returning null

What am I doing wrong ?

CodePudding user response：

Move the WHERE restrictions to the CASE expression as well:

SELECT ROUND(SUM(CASE WHEN t2.ELEMNAME IN ('A','B','C')
                      THEN NBTOSUM ELSE 0 END) / 1000000, 1)
FROM MyTable t2;

Note that this trick solves the null problem and also avoids the need for an ugly COALESCE() call.

CodePudding user response：

Your code should work as the SUM aggregation function will generate a single row of output regardless of whether the number of input rows is zero or non-zero. If there are no input rows or the values are all NULL then the output of the SUM will be NULL and then COALESCE would work.

Since you claim it does not then that suggests that there is something else going on in your query that you have not shared in the question.

You have braces around your statement suggesting that you are using it as part of a larger statement. If so, you can try moving the COALESCE to the outer query:

SELECT COALESCE(
         (
           SELECT ROUND(SUM(NBTOSUM)/1000000,1)
           FROM   MyTable
           WHERE  ELEMNAME IN ('A','B','C')
         ),
         0
       )
FROM   your_outer_query;

That might fix the problem if you are somehow correlating to an outer query but your question makes no mention of that.

fiddle