SQLのGROUP BYでNULLを扱う方法：集計関数とCOALESCEの注意点

GROUP BYの対象列にNULLがあっても、そのまま集計できます。1列でグループ化する場合、その列がNULLの行は1つのグループになります。NULLがあるだけで分類が不定になるわけではなく、COALESCEによる置き換えも必須ではありません。この記事では、比較とグループ化の違い、集計関数の結果、表示用ラベルの注意点を実行例で確認します。

SQLのNULL：通常の比較とGROUP BYは別のルール

NULLは「不明」「未入力」など、値がない状態を表すマーカーです。NULLを許す列にもデータ型はあります。また、MySQL・PostgreSQL・SQLiteでは、数値の0や空文字列とNULLは別です。

WHERE句は条件がTRUEの行だけを残すため、NULLの行を探すときは WHERE department IS NULL と書きます。「NULLを含む比較は常にUNKNOWN」ではなく、=、<>などの通常の比較についての説明です。PostgreSQLの IS NOT DISTINCT FROM やMySQLの <=> では、両辺がNULLの場合もTRUEとして比較できます。

根拠：MySQL：NULLの比較とGROUP BY、PostgreSQL：比較演算子、MySQL：NULL安全な比較、SQLite：DISTINCT。

実践例：NULLを置き換えずにGROUP BYする

以下は練習用の空のデータベースで一度だけ実行するサンプルです。既存の業務テーブルには実行しないでください。部門・地域・金額を持つ8行を用意し、NULLとは別に、実際の文字列 '未指定' も含めます。金額は説明用の整数です。

CREATE TABLE employee_null_demo (
  id INTEGER PRIMARY KEY,
  department VARCHAR(20),
  region VARCHAR(20),
  amount INTEGER
);

INSERT INTO employee_null_demo (id, department, region, amount)
VALUES
  (1, '営業',   '東京', 100),
  (2, '営業',   '東京', NULL),
  (3, NULL,     '東京', 80),
  (4, NULL,     '大阪', NULL),
  (5, '未指定', '東京', 120),
  (6, '開発',   '東京', NULL),
  (7, '開発',   '東京', NULL),
  (8, NULL,     '東京', 40);

SELECT department,
       COUNT(*) AS employee_count,
       COUNT(amount) AS amount_count,
       SUM(amount) AS total_amount,
       AVG(amount) AS average_amount
FROM employee_null_demo
GROUP BY department;

結果は次の4グループです。以下の表のNULLは、文字列ではなくSQLのNULLを表します。ORDER BYを指定していないため、表示される行の順番は保証されません。

COUNT(*)はグループ内の全行数、COUNT(amount)は金額がNULLでない行数です。NULL件数は COUNT(*) - COUNT(amount) で求められます。SUM・AVG・MIN・MAXはNULL以外の入力値を対象とし、対象の値が1つもなければNULLを返します。

部門がNULLのグループは3人ですが、金額の平均は (80 + 40) / 2 = 60 です。開発部門は2行あっても金額がすべてNULLなので、金額の件数は0、合計と平均はNULLです。また、このデータで SELECT DISTINCT department はNULLを含めて4種類を返す一方、COUNT(DISTINCT department) はNULLを除くため3になります。

根拠：MySQL：COUNT・SUM・AVGなどの集計関数、SQLite：集計関数。

複数列でGROUP BYするときは、キーの組み合わせで分かれる

SELECT department, region, COUNT(*) AS employee_count
FROM employee_null_demo
GROUP BY department, region;

departmentがNULLでも、地域が異なる行は別グループです。同じキーの組み合わせにあるNULL同士はまとめられます。「NULLを含む行はすべて1つになる」のは1列だけで分類する場合の説明であり、複数列では全キーの組み合わせを確認します。SQLiteのGROUP BYの仕様でも、この規則が明記されています。

「全行がNULL」と「対象行が0件」を区別する

GROUP BYのない集計で対象行が0件になると、COUNTは0、SUM・AVG・MIN・MAXはNULLを返します。次の1つ目のクエリは1行を返しますが、2つ目のように列でGROUP BYすると、作れるグループがないので結果は0行です。

SELECT COUNT(*) AS employee_count,
       COUNT(amount) AS amount_count,
       SUM(amount) AS total_amount,
       AVG(amount) AS average_amount
FROM employee_null_demo
WHERE 1 = 0;
-- 結果は1行: 0, 0, NULL, NULL

SELECT department, COUNT(*) AS employee_count
FROM employee_null_demo
WHERE 1 = 0
GROUP BY department;
-- 結果は0行

結果がNULLのときに画面上だけ0を表示する仕様なら、COALESCE(SUM(amount), 0)を使えます。ただし、不明な金額と実際の0を同じ意味にしてよいか確認してください。特に AVG(COALESCE(amount, 0)) はNULLを0として平均の分母にも含めます。営業部門では AVG(amount)の100に対して50となり、集計の意味が変わります。COALESCE(AVG(amount), 0)のように集計後だけ置き換える式とは異なります。

また、SUM(COALESCE(amount, 0))でも、入力が0行なら足す値がないためNULLです。空の入力への対応と、各行のNULLの補完は分けて考えます。根拠：PostgreSQL：空の入力に対する集計結果、SQLite：SUM。

COALESCE・CASE・IFNULLは「表示」と「分類」を分けて使う

元の分類を維持し、表示だけ「未指定」にする

COALESCEは、引数のうち最初のNULLでない値を返します。元の列でGROUP BYしたまま、SELECTで表示名を補えば、NULLと実データの分類は変わりません。ただし表示名だけでは見分けられないため、元の値やNULL判定フラグも残します。

SELECT department,
       COALESCE(department, '未指定') AS department_label,
       CASE WHEN department IS NULL THEN 1 ELSE 0 END AS is_missing,
       COUNT(*) AS employee_count
FROM employee_null_demo
GROUP BY department;

この結果では、NULLの部門は「未指定・is_missing=1・3人」、実データの '未指定' は「未指定・is_missing=0・1人」として別々に残ります。表示名だけを使って後から再集計すると再び混ざるため、元のキーまたは判定フラグも引き継ぎます。

変換後の値でGROUP BYすると、実データと合流する

SELECT COALESCE(department, '未指定') AS department_label,
       COUNT(*) AS employee_count
FROM employee_null_demo
GROUP BY COALESCE(department, '未指定');

こちらはNULLの3行と実際に「未指定」の1行が合わさり、「未指定」が4人になります。他は営業2人、開発2人で、合計3グループです。業務上同じ区分として扱いたい場合には使えますが、NULL対策として無条件に適用すると分類を変えてしまいます。

CASE・MySQLのIFNULLとデータ型の注意

表示用の COALESCE(department, '未指定') は、CASE WHEN department IS NULL THEN '未指定' ELSE department END とも書けます。MySQLでは IFNULL(department, '未指定') も使えます。どの式でも、元の列でGROUP BYするか、変換後の式でGROUP BYするかが重要です。IFNULLはすべてのDBで共通の名前ではないため、移植性が必要ならCOALESCEを検討します。

COALESCEの引数やCASEの戻り値には、互換性のある型を使います。たとえばPostgreSQLでは共通の結果型に変換できる必要があり、数値列に '未指定' を混ぜるとエラーになることがあります。MySQLにも独自の結果型決定規則があるため、暗黙変換に頼らないようにします。数値の分類キーを0に置き換える場合も、本来の0とNULLが合流しないか確認してください。

根拠：PostgreSQL：CASE・COALESCEと結果型、MySQL：CASE・IFNULLと結果型。

ETL・入力時の前処理は、欠損の意味を確認してから

GROUP BYのためだけに、保存済みのNULLを一律に文字列や0へ置き換える必要はありません。ETLで置き換えると、「入力がなかった」「未確定」「業務上の未指定」などの区別を失う場合があります。表示目的ならSELECT側で補い、データの補完が必要な場合だけ業務ルールに沿って実施します。後から検証できるよう、必要に応じて元の値や欠損フラグを保持します。

まとめ

• NULLの比較にはIS NULLを使い、通常の比較とGROUP BYの規則を混同しない。
• 集計ではCOUNT(*)とCOUNT(列)、全NULLと0行、平均の分母を確認する。
• COALESCE・CASE・IFNULLによる表示変更と、グループキーの変更を区別する。

掲載SQLの共通部分と結果はSQLite 3.53.1のメモリ上のサンプルで確認しました。MySQL・PostgreSQLについてはリンク先の公式仕様を参照しており、これらのDBでの実行検証はしていません。結果の小数表記・戻り値の型や文字列の比較規則は、DBや列の設定により異なります。