【MySQL】すべての行がNULLでない場合のみ合計し、NULLが含まれる場合はNULLを返す方法

はじめに

MySQLでグループごとの合計値を計算する際、「そのグループに含まれるすべての行がNULLでない場合だけ合計し、1つでもNULLが含まれていればNULLを返したい」という要件に出会うことがあります。このような処理は、GROUP BY句とHAVING句を組み合わせることで簡単に実現できます。

基本となる構文

以下の構文を使用します。

SELECT yourColumnName1,
    SUM(yourColumnName2)
FROM yourTableName
GROUP BY yourColumnName1
HAVING COUNT(yourColumnName2) = COUNT(*);

ポイントはHAVING句の条件「COUNT(列名) = COUNT(*)」です。COUNT(列名)はNULL以外の値の件数を数え、COUNT(*)はNULLも含めた全行数を数えます。この2つが一致する＝NULLが1つも存在しないグループだけが、結果セットに残る仕組みです。

サンプルテーブルの作成

実際の動作を確認するために、まずテーブルを作成しましょう。

mysql> create table SumDemo
    -> (
    -> Id int,
    -> Amount int
    -> );
Query OK, 0 rows affected (0.58 sec)

次に、INSERTコマンドでレコードを挿入します。検証用に、NULLを含むデータも意図的に用意しています。

mysql> insert into SumDemo values(1,200);
Query OK, 1 row affected (0.22 sec)
mysql> insert into SumDemo values(2,100);
Query OK, 1 row affected (0.19 sec)
mysql> insert into SumDemo values(2,NULL);
Query OK, 1 row affected (0.14 sec)
mysql> insert into SumDemo values(1,300);
Query OK, 1 row affected (0.16 sec)
mysql> insert into SumDemo values(2,100);
Query OK, 1 row affected (0.17 sec)
mysql> insert into SumDemo values(1,500);
Query OK, 1 row affected (0.16 sec)

テーブルの中身を確認する

SELECT文ですべてのレコードを表示してみます。

mysql> select *from SumDemo;

出力結果

+------+--------+
| Id   | Amount |
+------+--------+
|    1 |    200 |
|    2 |    100 |
|    2 |   NULL |
|    1 |    300 |
|    2 |    100 |
|    1 |    500 |
+------+--------+
6 rows in set (0.00 sec)

すべての行がNULLでない場合のみ合計するクエリ

それでは、本題のクエリを実行してみましょう。

mysql> select Id,
    -> SUM(Amount)
    -> from SumDemo
    -> GROUP BY ID
   -> HAVING COUNT(Amount) = COUNT(*);

実行結果

+------+-------------+
| Id   | SUM(Amount) |
+------+-------------+
|    1 |        1000 |
+------+-------------+
1 row in set (0.09 sec)

結果の解説

Id=2のグループにはNULLが含まれているため、HAVING句の条件を満たさず、結果から除外されました（NULLを含むグループは合計対象にならないため、実質的にNULLが返されるのと同じ扱いです）。一方、Id=1のグループはすべての行に値が存在するため、200 + 300 + 500 = 1000という合計値が出力されています。

まとめ

HAVING COUNT(列名) = COUNT(*)という比較を使うことで、「NULLを含まないグループだけを合計する」という処理をシンプルに記述できます。データの欠損チェックを兼ねた集計処理などに応用できる便利なテクニックなので、ぜひ覚えておきましょう。