mysql求和null,MySQL-仅在所有行都不为null时求和，否则返回null

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!

解决方案

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.