MySQL - Sum only if all rows are not null, else return null

Question

Let's suppose the following table:

 X   VALUE
 1   2
 1   3
 2   NULL
 2   4

I want a result set grouped by X with the sum of VALUE, but only if all the rows related to each X value are not null.

Using the same example, the result must be:

X   SUM(VALUE)
1   5

As you can see, X=2 is not selected due to the (2, NULL) tuple.

I want, whenever this is possible, without using subqueries.

Thank you very much!

Canoness

Answer 1

You can achieve that with:

SELECT 
  x, 
  SUM(value) 
FROM 
  t 
GROUP BY 
  x 
HAVING 
  COUNT(value)=COUNT(*)

This will work following way: group values in normal way, but then compare entire count (so * points to that) with column count (which won't include NULL-s). If they are not equal, then there are NULL's and value shouldn't be included.

Colley

i wish i could upvote this twice. thanks, great answer - simple but not obvious – Mullion

Answer 2

Another way is this. It may or may not be faster:

SELECT X, IF(SUM(VALUE is NULL), NULL, SUM(VALUE)) as value
FROM table_name
group by X
having not(null <=> value);

Also, with this method, if you remove the having clause you get all rows, but with null values for the summed columns with nulls which you then could conditionally do something else with in your script.

http://sqlfiddle.com/#!2/f769cc/18

Debacle

Answer 3

This whould work:

SELECT SUM(value) FROM test WHERE value IS NOT NULL GROUP BY X

Campeche

did you check your answer? – Membership

This will just exclude NULL's from entire group, but won't affect non-null values in calculating aggregate results – Colley