SQL query to return NULL for SUM (expression) when the included value is IS NULL

Question

For MySQL, I need a query that returns SUM expressions, EXCEPT, that I want the return value to be NULL if any of the expressions included in SUM is NULL. (The normal SUM operation is to ignore NULL values.

Here is a simple test case illustrating

CREATE TABLE t4 (fee VARCHAR(3), fi INT); INSERT INTO t4 VALUES ('fo',10),('fo',200),('fo',NULL),('fum',400); SELECT fee, SUM(fi) AS sum_fi FROM t4 GROUP BY fee 

This returns exactly the result that I expect:

 fee sum_fi --- ------ fo 210 fum 400 

What I want is a query that returns a DIFFERENT result set:

 fee sum_fi --- ------ fo NULL fum 400 

I want the value returned for sum_fi to be NULL when the included value is NULL. (This is different from the SUM aggregate function, ignores NULL, and returns the final value for non-NULL expressions.)

Question: What query can I use to return the desired result set?

I do not see the built-in aggregate function that exhibits this behavior.

Answer 1

How about using SUM(fi is NULL) to determine if you have a NULL value in your dataset? This should work:

 SELECT fee, IF(SUM(fi is NULL), NULL, SUM(fi)) AS sum_fi FROM t4 GROUP BY fee 

Answer 2

You can use this query:

 SELECT fee, IF(COUNT(fi) < (SELECT count(fee) FROM t4 temp2 WHERE temp1.fee = temp2.fee), NULL, SUM(fi)) AS sum_fi FROM t4 temp1 GROUP BY fee 

There is also this solution:

 SELECT fee, CASE WHEN COUNT(*) = COUNT(fi) THEN SUM(fi) ELSE NULL END AS sum_fi FROM t4 GROUP BY fee 

which I got from answering Aaron W's question, marked as a duplicate of this. Admittedly, the latter form looks better than my original idea.