SQL集計関数一覧｜SUM・AVG・MAX・MIN・COUNTとNULLの扱い

SQLの集計関数とは、複数の行をまとめて1つの値（合計・平均・最大・最小・件数など）を返す関数です。 代表は SUM・AVG・MAX・MIN・COUNT の5つで、どのデータベースでも同じ名前で使えます。

5つとも書き方はシンプルですが、NULL（値が入っていない状態）があると結果が変わる点でつまずきやすい関数でもあります。この記事では、NULL を含む6行のサンプル表を用意し、COUNT(*)・COUNT(列)・COUNT(DISTINCT 列)・SUM・AVG・MAX・MIN の結果を1つの表で見比べられるようにしました。すべて MySQL 8.0.46・PostgreSQL 16.15・SQLite 3.45.1 で実際に実行した結果です。文字列をつなげる集計（GROUP_CONCAT / STRING_AGG / LISTAGG）などDBごとに名前が違う関数の対応表、GROUP BY・HAVING・ウィンドウ関数としての使い方まで1ページにまとめています。

集計関数の早見表

サンプル表（6行、金額に NULL が2行）での実際の結果は次のとおりです。

この記事で使うサンプルテーブル

売上を記録する sales テーブルです。NULL の動きが見えるよう、金額が空の行と、カテゴリも金額も空の行を入れてあります。次のSQLは MySQL・PostgreSQL・SQLite のどれでもそのまま動きます。

CREATE TABLE sales (
  id INT PRIMARY KEY,
  staff VARCHAR(20),
  category VARCHAR(20),
  amount INT
);

INSERT INTO sales VALUES
(1,'Aoki','book',1000),
(2,'Aoki','book',3000),
(3,'Aoki','pen',NULL),
(4,'Baba','pen',2000),
(5,'Baba','pen',2000),
(6,'Chiba',NULL,NULL);

7つの結果を1つのSQLで見比べる

まず、主な集計関数を1つの SELECT に並べて実行します。

SELECT COUNT(*)               AS cnt_all,
       COUNT(amount)          AS cnt_col,
       COUNT(DISTINCT amount) AS cnt_dist,
       SUM(amount)            AS sum_amt,
       AVG(amount)            AS avg_amt,
       MAX(amount)            AS max_amt,
       MIN(amount)            AS min_amt
FROM sales;

実行結果（MySQL 8.0.46）：

+---------+---------+----------+---------+-----------+---------+---------+
| cnt_all | cnt_col | cnt_dist | sum_amt | avg_amt   | max_amt | min_amt |
+---------+---------+----------+---------+-----------+---------+---------+
|       6 |       4 |        3 |    8000 | 2000.0000 |    3000 |    1000 |
+---------+---------+----------+---------+-----------+---------+---------+

PostgreSQL 16.15 では avg_amt が 2000.0000000000000000、SQLite 3.45.1 では 2000.0 と表示され、値そのものは3つのDBで同じでした。違うのは平均の表示桁数だけです。

表示をそろえたいときは ROUND(AVG(amount), 1) のように丸めます（PostgreSQL 16.15 で 2000.0 になることを確認）。

COUNTの3つの書き方と数え方の差

COUNT は、かっこの中に何を書くかで数える対象が変わります。

「行数が知りたいときは COUNT(*)、値が入っている件数が知りたいときは COUNT(列)」と使い分けます。COUNT(*) と COUNT(列) の差（6 − 4 = 2）は、その列が NULL の行数です。未入力の件数を調べるときにそのまま使えます。

-- 金額が未入力の行数を出す
SELECT COUNT(*) - COUNT(amount) AS null_rows FROM sales;

COUNT(DISTINCT 列) は重複を除いて数えます。サンプルでは 2000 が2行ありますが、1種類として数えられるので 3 になります。「何人の担当者が売上を登録したか」「何種類のカテゴリがあるか」といった問いに向いています。

AVGはNULLの行を分母から外す

AVG は「合計 ÷ 件数」ですが、このときの件数は NULL でない行の数です。サンプルでは 8000 ÷ 4 = 2000 になり、6 行で割った 1333.33… にはなりません。

「未入力は 0 として平均に入れたい」場合は、COALESCE（NULL を別の値に置き換える関数）で 0 にしてから平均します。3つの計算方法を並べてみます。

SELECT AVG(amount)               AS avg_skip_null,
       AVG(COALESCE(amount, 0))  AS avg_null_as_0,
       SUM(amount) / COUNT(*)    AS sum_div_rows
FROM sales;

実行結果（MySQL 8.0.46）：

+---------------+---------------+--------------+
| avg_skip_null | avg_null_as_0 | sum_div_rows |
+---------------+---------------+--------------+
|     2000.0000 |     1333.3333 |    1333.3333 |
+---------------+---------------+--------------+

実行結果（PostgreSQL 16.15）：

     avg_skip_null     |     avg_null_as_0     | sum_div_rows
-----------------------+-----------------------+--------------
 2000.0000000000000000 | 1333.3333333333333333 |         1333
(1 row)

3つめの式で DB ごとの差が出ました。PostgreSQL と SQLite は整数どうしの割り算の結果を整数にするため、小数点以下が切り捨てられて 1333 になります。小数まで欲しいときは SUM(amount) * 1.0 / COUNT(*) のように片方を小数にするか、AVG(COALESCE(amount, 0)) を使うと、どのDBでも同じ結果になります。

どちらの平均が正しいかは、NULL の意味で決まります。

対象が0件のときSUMはNULLになる

WHERE で絞り込んだ結果、1行も残らなかった場合の動きです。存在しない担当者で集計してみます。

SELECT COUNT(*)                  AS cnt,
       SUM(amount)               AS sum_amt,
       AVG(amount)               AS avg_amt,
       MAX(amount)               AS max_amt,
       COALESCE(SUM(amount), 0)  AS sum_or_0
FROM sales
WHERE staff = 'Nobody';

実行結果（MySQL 8.0.46。PostgreSQL 16.15・SQLite 3.45.1 でも同じ内容）：

+-----+---------+---------+---------+----------+
| cnt | sum_amt | avg_amt | max_amt | sum_or_0 |
+-----+---------+---------+---------+----------+
|   0 |    NULL |    NULL |    NULL |        0 |
+-----+---------+---------+---------+----------+

COUNT だけが 0 を返し、SUM・AVG・MAX・MIN は NULL を返します。 「合計は 0 になるはず」と考えてアプリ側で数値として受け取ると、NULL の扱いで想定外の動きになることがあります。

対処は、集計関数の外側を COALESCE で包むことです。

-- 0件でも 0 を返す
SELECT COALESCE(SUM(amount), 0) AS total FROM sales WHERE staff = 'Nobody';

COALESCE を置く場所で意味が変わるので、整理しておきます。

0件への備えとして効くのは2行目の「外側に置く」形です。3行目は各行の値を置き換えているだけなので、行が1つもなければ結果は NULL のままです。

同じことが、GROUP BY でグループの中の値がすべて NULL のときにも起こります（次の章の Chiba の行）。

GROUP BYでグループごとに集計する

GROUP BY を付けると、指定した列の値ごとに行をまとめ、グループごとに集計関数が計算されます。

SELECT staff,
       COUNT(*)      AS cnt,
       COUNT(amount) AS cnt_amt,
       SUM(amount)   AS sum_amt,
       AVG(amount)   AS avg_amt
FROM sales
GROUP BY staff
ORDER BY staff;

実行結果（MySQL 8.0.46）：

+-------+-----+---------+---------+-----------+
| staff | cnt | cnt_amt | sum_amt | avg_amt   |
+-------+-----+---------+---------+-----------+
| Aoki  |   3 |       2 |    4000 | 2000.0000 |
| Baba  |   2 |       2 |    4000 | 2000.0000 |
| Chiba |   1 |       0 |    NULL |      NULL |
+-------+-----+---------+---------+-----------+

• Aoki は3行ありますが、金額が入っているのは2行なので、平均は 4000 ÷ 2 = 2000 です
• Chiba は行が1つあるのに、金額が NULL だけなので SUM も AVG も NULL になります。0 と表示したいときは COALESCE(SUM(amount), 0) にします

GROUP BYの列がNULLのとき

グループ分けに使う列が NULL の行は、NULL どうしで1つのグループにまとめられます。

SELECT category, COUNT(*) AS cnt, SUM(amount) AS sum_amt
FROM sales
GROUP BY category
ORDER BY category;

実行結果（PostgreSQL 16.15）：

 category | cnt | sum_amt
----------+-----+---------
 book     |   2 |    4000
 pen      |   3 |    4000
          |   1 |
(3 rows)

カテゴリが空の行が1グループとして出ています。なお、ORDER BY での NULL の位置はDBによって異なり、PostgreSQL では最後、MySQL 8.0.46 と SQLite 3.45.1 では先頭に表示されました。

SELECTに書ける列のルール

GROUP BY を使うとき、SELECT に書けるのは「GROUP BY に書いた列」と「集計関数」です。それ以外の列を書くと、グループの中のどの行の値を出せばよいかが決まらないため、エラーになります。

-- category は GROUP BY にも集計関数にも入っていない
SELECT staff, category, SUM(amount) FROM sales GROUP BY staff;

PostgreSQL 16.15 のエラー文：

ERROR:  column "sales.category" must appear in the GROUP BY clause or be used in an aggregate function

MySQL 8.0.46 のエラー文（既定の sql_mode に ONLY_FULL_GROUP_BY が含まれる場合）：

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't200.sales.category' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

直し方は目的によって3つあります。

HAVINGで集計結果を絞り込む

「合計が 4000 以上の担当者だけ」のように、集計した結果に条件を付けるときは HAVING を使います。

SELECT staff, SUM(amount) AS sum_amt
FROM sales
GROUP BY staff
HAVING SUM(amount) >= 4000
ORDER BY staff;

実行結果（3つのDBで同じ）：

 staff | sum_amt
-------+---------
 Aoki  |    4000
 Baba  |    4000
(2 rows)

Chiba は合計が NULL なので、NULL >= 4000 が真にならず、結果から外れます。

WHEREに集計関数を書くとエラーになる

WHERE は「集計する前の行」を絞る場所なので、集計関数は書けません。

SELECT staff, amount FROM sales WHERE amount > AVG(amount);

-- PostgreSQL 16.15
ERROR:  aggregate functions are not allowed in WHERE

-- MySQL 8.0.46
ERROR 1111 (HY000): Invalid use of group function

「平均より大きい行」「最大の行」を取り出したいときは、サブクエリ（SQLの中に入れた別のSELECT）で先に集計します。

-- 金額が最大の行を取り出す
SELECT staff, amount FROM sales
WHERE amount = (SELECT MAX(amount) FROM sales);

 staff | amount
-------+--------
 Aoki  |   3000
(1 row)

WHEREとHAVINGの違い

SELECT 文は、書く順番とは別に、次の順番で処理されます。

flowchart LR
    A[FROM] --> B[WHERE] --> C[GROUP BY] --> D[HAVING] --> E[SELECT] --> F[ORDER BY]

集計に関係しない条件（カテゴリや日付での絞り込み）は WHERE に書くと、集計する行数が先に減るので効率的です。

条件つきの集計（CASEとFILTER）

「2000 以上の件数」と「pen の合計」のように、条件の違う集計を1回のSQLで並べたいときは、集計関数の中に CASE を書きます。

SELECT COUNT(CASE WHEN amount >= 2000 THEN 1 END)                  AS ge2000,
       SUM(CASE WHEN category = 'pen' THEN amount ELSE 0 END)      AS pen_total
FROM sales;

実行結果（3つのDBで同じ）：

 ge2000 | pen_total
--------+-----------
      3 |      4000
(1 row)

COUNT(CASE WHEN … THEN 1 END) は、条件に合わない行が NULL になり、COUNT が NULL を数えない性質を利用しています。NULL の扱いを知っていると、こうした書き方の意味も読み取れるようになります。

PostgreSQL では、同じことを FILTER で書けます。

-- PostgreSQL 16.15 で確認
SELECT staff, COUNT(*) FILTER (WHERE amount >= 2000) AS ge2000
FROM sales GROUP BY staff ORDER BY staff;

 staff | ge2000
-------+--------
 Aoki  |      1
 Baba  |      2
 Chiba |      0
(3 rows)

MAXとMINは文字列や日付にも使える

MAX と MIN は数値以外にも使えます。文字列では辞書順、日付では新しい・早い順で比べます。

SELECT SUM(DISTINCT amount) AS sum_dist,
       MAX(staff)           AS max_staff,
       MIN(staff)           AS min_staff
FROM sales;

 sum_dist | max_staff | min_staff
----------+-----------+-----------
     6000 | Chiba     | Aoki
(1 row)

SUM(DISTINCT amount) は重複を除いて合計するため、1000＋2000＋3000 = 6000 です。2000 が1回しか足されない点に注意して使います。

MIN は NULL を「最小」とは見なしません。NULL は比べる対象から外れます。

ウィンドウ関数として集計関数を使う

GROUP BY は行をまとめてしまいますが、OVER を付けると、元の行を残したまま集計結果を横に並べられます。 これがウィンドウ関数としての使い方です。

SELECT id, staff, amount,
       SUM(amount) OVER (PARTITION BY staff) AS staff_total,
       SUM(amount) OVER (ORDER BY id)        AS running,
       COUNT(*)    OVER ()                   AS all_rows
FROM sales
ORDER BY id;

実行結果（MySQL 8.0.46。PostgreSQL 16.15・SQLite 3.45.1 でも同じ内容）：

+----+-------+--------+-------------+---------+----------+
| id | staff | amount | staff_total | running | all_rows |
+----+-------+--------+-------------+---------+----------+
|  1 | Aoki  |   1000 |        4000 |    1000 |        6 |
|  2 | Aoki  |   3000 |        4000 |    4000 |        6 |
|  3 | Aoki  |   NULL |        4000 |    4000 |        6 |
|  4 | Baba  |   2000 |        4000 |    6000 |        6 |
|  5 | Baba  |   2000 |        4000 |    8000 |        6 |
|  6 | Chiba |   NULL |        NULL |    8000 |        6 |
+----+-------+--------+-------------+---------+----------+

ここでも NULL の扱いは同じです。id=3 の累計は NULL を飛ばして 4000 のまま、Chiba の担当者合計は値がすべて NULL なので NULL になっています。

DB別の関数名対応表

SUM・AVG・MAX・MIN・COUNT はどのDBでも共通ですが、それ以外の集計関数は名前や書き方が異なります。MySQL・PostgreSQL・SQLite は今回実行した書き方、SQL Server と Oracle は今回の環境で動かせなかったため未実行（一般に知られた仕様）です。

文字列をつなげる集計

-- MySQL 8.0.46
SELECT staff,
       GROUP_CONCAT(category ORDER BY id SEPARATOR ',') AS cats,
       GROUP_CONCAT(DISTINCT category)                  AS dcats
FROM sales GROUP BY staff ORDER BY staff;

+-------+---------------+----------+
| staff | cats          | dcats    |
+-------+---------------+----------+
| Aoki  | book,book,pen | book,pen |
| Baba  | pen,pen       | pen      |
| Chiba | NULL          | NULL     |
+-------+---------------+----------+

-- PostgreSQL 16.15（同じ結果。Chiba の行は空欄＝NULL で表示）
SELECT staff,
       STRING_AGG(category, ',' ORDER BY id) AS cats,
       STRING_AGG(DISTINCT category, ',')    AS dcats
FROM sales GROUP BY staff ORDER BY staff;

文字列の連結でも NULL は飛ばされ、すべて NULL のグループは結果も NULL になります。数値の集計と同じ考え方です。

そのほかの集計関数

この表のうち今回実行したのは、MySQL と PostgreSQL の STDDEV・VARIANCE・ROLLUP、MySQL の JSON_ARRAYAGG、PostgreSQL の ARRAY_AGG と FILTER、SQLite の json_group_array・FILTER・TOTAL、3つのDB共通の CASE 式と COALESCE です。SQL Server と Oracle の列、および「標準では用意されていない」と書いたセルは未実行です。

実行してわかった差を2つ紹介します。

1. 同じ STDDEV でも、MySQL と PostgreSQL で結果が違いました。

どちらの計算かを明示したいときは、STDDEV_POP（母集団）・STDDEV_SAMP（標本）のように名前で指定すると、DBを移っても意味が変わりません。MySQL 8.0.46 と PostgreSQL 16.15 の両方で、次の結果が一致することを確認しました。

SELECT STDDEV_POP(amount) AS pop, STDDEV_SAMP(amount) AS samp FROM sales;

+-------------------+------------------+
| pop               | samp             |
+-------------------+------------------+
| 707.1067811865476 | 816.496580927726 |
+-------------------+------------------+

値を配列にまとめる集計では、NULL も要素として残る点が SUM や COUNT と異なります。MySQL 8.0.46 の JSON_ARRAYAGG(amount) は [1000, 3000, null, 2000, 2000, null]、PostgreSQL 16.15 の ARRAY_AGG(amount) を担当者ごとに実行すると Aoki は {1000,3000,NULL} になりました。

2. SQLite の TOTAL は、0件のとき NULL ではなく 0.0 を返します。

-- SQLite 3.45.1
SELECT TOTAL(amount), SUM(amount) FROM sales WHERE staff = 'Nobody';

TOTAL(amount)|SUM(amount)
0.0|

小計と総計を足すROLLUP

ROLLUP を使うと、グループごとの集計に加えて総計の行が付きます。

-- MySQL 8.0.46
SELECT staff, SUM(amount) FROM sales GROUP BY staff WITH ROLLUP;

+-------+-------------+
| staff | SUM(amount) |
+-------+-------------+
| Aoki  |        4000 |
| Baba  |        4000 |
| Chiba |        NULL |
| NULL  |        8000 |
+-------+-------------+

最後の staff が NULL の行が総計です。PostgreSQL では GROUP BY ROLLUP(staff, category) と書き、担当者ごとの小計と全体の総計（8000）が出ることを確認しました。

結果が想定と違うときのチェックリスト

よくある質問

Q. COUNT(*) と COUNT(列) は何が違いますか？ A. COUNT(*) は行数を数え、COUNT(列) はその列が NULL でない行だけを数えます。サンプルでは 6 と 4 でした。差の 2 が、その列が NULL の行数です。

Q. AVGでNULLを0として平均に入れるにはどうしますか？ A. AVG(COALESCE(列, 0)) と書きます。サンプルでは、そのままの AVG が 2000、COALESCE を入れた AVG が 1333.33… でした。NULL が「未入力」なのか「0」なのかで、どちらを使うかを決めます。

Q. データがないときにSUMを0にしたいです。 A. COALESCE(SUM(列), 0) と、SUM の外側を COALESCE で包みます。SUM(COALESCE(列, 0)) は各行の NULL を 0 にするだけなので、行が1つもないときは NULL のままです。

Q. 集計関数はWHEREで使えますか？ A. 使えません。PostgreSQL では「aggregate functions are not allowed in WHERE」、MySQL では「Invalid use of group function」というエラーになります。集計結果で絞るなら HAVING、集計値と各行を比べるならサブクエリを使います。

まとめ

• 集計関数は SUM・AVG・MAX・MIN・COUNT の5つが基本で、どのDBでも同じ名前で使える
• COUNT(*) は行数、COUNT(列) は NULL でない行数、COUNT(DISTINCT 列) は値の種類数（サンプルで 6・4・3）
• AVG は NULL の行を分母から外す。0 として入れたいときは AVG(COALESCE(列, 0))
• 対象が0件だと COUNT は 0、SUM・AVG・MAX・MIN は NULL。COALESCE(SUM(列), 0) で 0 にできる
• 集計前の絞り込みは WHERE、集計後の絞り込みは HAVING
• OVER を付けるとウィンドウ関数になり、明細を残したまま合計や累計を並べられる
• 文字列連結は GROUP_CONCAT（MySQL・SQLite）／STRING_AGG（PostgreSQL・SQL Server）／LISTAGG（Oracle）とDBで名前が違う

株式会社RJCでは、SQLの集計関数とNULLの扱いを仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。