SQL の GROUP BY と HAVING の使い方 - WHERE との違い・集約関数・論理的な処理順序

SQL の GROUP BY と HAVING の使い方 - WHERE との違い・集約関数・論理的な処理順序

作成日:
読了:約54分
更新日:

当サイトは Amazon.co.jp を宣伝しリンクすることで紹介料を得る手段を提供する、Amazonアソシエイト・プログラムの参加者です。価格・在庫はリンク先の最新情報をご確認ください。

店舗ごとの売上合計を出す、月ごとの注文件数を数える、売上が一定額以上の店舗だけを残す。集計のクエリは日常的に書くのに、「WHERE に集約関数を書いたらエラーになった」「SELECT に列を1つ足しただけで怒られた」「MySQL では動いたのに PostgreSQL では動かない」と手が止まる場面がよくあります。

こうしたつまずきの多くは、SQL の各句が決まった順番で評価されることを押さえると説明できます。WHERE はグループを作る前に行を絞り、HAVING はグループを作った後にグループを絞ります。SELECT の別名が使える場所・使えない場所も、この順番から決まります。

この記事では、小さなサンプルテーブルを最初に1回だけ定義し、すべてのクエリをそのデータに対して実行して結果を並べます。GROUP BY の基本と集約関数から始めて、WHERE と HAVING の違い、製品ごとの差、小計・総計の出し方、性能の考え方までを順に確認します。

NOTE

記事中の結果表は、特記のない限り SQLite 3.54.0 と PostgreSQL 18.6 の両方で実行し、同じ結果になることを確かめたものです(AVG の小数点以下の表示桁数だけは製品で異なり、表には SQLite の出力を載せています)。結果表の NULL は NULL 値を表します。FILTER 以外の PostgreSQL 固有の構文(ROLLUP など)は PostgreSQL 18.6 だけで実行しました。MySQL の挙動は MySQL 8.4 リファレンスマニュアルで確認した内容を載せています(手元での実行はしていません)。

先に結論 - 早見表

やりたいこと書き方注意点
グループごとに集計するGROUP BY 列 + 集約関数SELECT に書ける非集約列は原則 GROUP BY に書いた列だけ
行を数えるCOUNT(*)NULL の行も数える
値の入っている行を数えるCOUNT(列)NULL の行は数えない
種類の数を数えるCOUNT(DISTINCT 列)NULL は種類に含まない
集計する前に行を絞るWHERE集約関数は書けない
集計した結果でグループを絞るHAVING集約関数を使う条件はこちら
条件ごとに別々に集計するFILTER (WHERE ...) または CASE 式FILTER は PostgreSQL と SQLite。MySQL 8.4 のリファレンスでは記載を確認できず
小計と総計を同時に出すROLLUP / GROUPING SETS / CUBEMySQL は WITH ROLLUP(または ROLLUP (...))。SQLite は非対応で UNION ALL で代用
行を残したまま集計値を並べるウィンドウ関数(OVER)GROUP BY と違って行数が減らない
0 件のグループも出す親テーブルから LEFT JOIN して COUNT(子の列)COUNT(*) だと 0 件のグループが 1 になる

サンプルデータ

以降の例はすべて次のデータを使います。サンプル商店(仮)の3店舗と、その売上明細です。

サンプルテーブルの定義とデータ
CREATE TABLE shops (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
 
CREATE TABLE sales (
  id          INTEGER PRIMARY KEY,
  shop_id     INTEGER NOT NULL REFERENCES shops(id),
  category    TEXT NOT NULL,
  amount      INTEGER,          -- 金額未確定は NULL
  customer_id INTEGER           -- ゲスト購入は NULL
);
 
INSERT INTO shops VALUES (1, 'A店'), (2, 'B店'), (3, 'C店');
 
INSERT INTO sales VALUES
  (101, 1, '食品', 1200, 1),
  (102, 1, '食品',  800, 2),
  (103, 1, '雑貨', 3000, 1),
  (104, 1, '雑貨', 1500, NULL),
  (105, 2, '食品',  500, NULL),
  (106, 2, '雑貨', 2500, 3),
  (107, 2, '雑貨', NULL, 3);

shops テーブル:

idname
1A店
2B店
3C店

sales テーブル:

idshop_idcategoryamountcustomer_id
1011食品12001
1021食品8002
1031雑貨30001
1041雑貨1500NULL
1052食品500NULL
1062雑貨25003
1072雑貨NULL3

ポイントは次の3つです。

  • C店には売上が1件もない
  • 売上 107 は amount が NULL(金額未確定)
  • 売上 104 と 105 は customer_id が NULL(ゲスト購入)

GROUP BY の基本 - 行をグループにまとめて1行にする

GROUP BY は、指定した列の値が同じ行を1つのグループにまとめ、グループごとに1行の結果を返します。まとめた行の中身は、COUNT や SUM などの集約関数で1つの値にします。

店舗ごとの件数と売上合計
SELECT shop_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
GROUP BY shop_id
ORDER BY shop_id;
shop_idcnttotal
146500
233000

7行の明細が、shop_id の値ごとに2行へまとまりました。A店は 1200 + 800 + 3000 + 1500 = 6500、B店は 500 + 2500 = 3000 です(B店の NULL の行は SUM で飛ばされます。後述)。

GROUP BY に複数の列を並べると、値の組み合わせごとにグループができます。

店舗 x カテゴリごとの集計
SELECT shop_id, category, COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
GROUP BY shop_id, category
ORDER BY shop_id, category;
shop_idcategorycnttotal
1雑貨24500
1食品22000
2雑貨22500
2食品1500

ここで、結果に C店(shop_id が 3)が出てこない点に注目してください。GROUP BY は、集計対象の行に実際に存在する値からしかグループを作りません。売上のない店舗も 0 件として出したい場合は、店舗テーブルを起点に LEFT JOIN します(後の節で扱います)。

NOTE

ORDER BY を書かない場合、結果の行の順番は保証されません。PostgreSQL の SELECT のドキュメントは、ORDER BY がなければ行はシステムが最も速く返せる順で返る、としています。GROUP BY した結果がたまたま並んで見えても、順番が必要なら ORDER BY を書きます。

集約関数と NULL

よく使う集約関数を、テーブル全体(1つのグループ)に対して一度に実行してみます。GROUP BY を書かずに集約関数を使うと、全行が1つのグループとして扱われます。

テーブル全体の集計
SELECT COUNT(*)                    AS all_rows,
       COUNT(amount)               AS cnt_amount,
       COUNT(customer_id)          AS cnt_customer,
       COUNT(DISTINCT customer_id) AS distinct_customers,
       SUM(amount)                 AS total,
       AVG(amount)                 AS avg_amount,
       MIN(amount)                 AS min_amount,
       MAX(amount)                 AS max_amount
FROM sales;
all_rowscnt_amountcnt_customerdistinct_customerstotalavg_amountmin_amountmax_amount
765395001583.33333333333335003000

COUNT(*) と COUNT(列) と COUNT(DISTINCT 列)

  • COUNT(*) は 7。行そのものを数えるので、NULL を含む行も数える
  • COUNT(amount) は 6。amount が NULL の売上 107 を数えない
  • COUNT(customer_id) は 5。ゲスト購入の 104 と 105 を数えない
  • COUNT(DISTINCT customer_id) は 3。値の種類(1、2、3)を数え、NULL は種類に含めない

PostgreSQL の集約関数のドキュメントでも、count(*) は入力行の数、count(式) は式の値が NULL ではない入力行の数、と区別されています。「件数」を出したいのか、「値が入っている件数」を出したいのか、「何種類あるか」を出したいのかで使い分けます。

SUM・AVG・MIN・MAX は NULL を飛ばす

SUM・AVG・MIN・MAX は、NULL の値を飛ばして計算します。AVG(amount) は、合計 9500 を 7 ではなく 6 で割った 1583.33 です。

店舗ごとに見ると、差がよりはっきりします。

店舗ごとの件数と平均
SELECT shop_id,
       COUNT(*)      AS cnt,
       COUNT(amount) AS cnt_amount,
       SUM(amount)   AS total,
       AVG(amount)   AS avg_amount
FROM sales
GROUP BY shop_id
ORDER BY shop_id;
shop_idcntcnt_amounttotalavg_amount
14465001625.0
23230001500.0

B店の平均は 3000 / 2 = 1500 です。金額未確定の行を「0 円の売上」として平均に含めたいなら、AVG(COALESCE(amount, 0)) のように明示的に置き換えます(3000 / 3 = 1000 になります)。どちらが正しいかは NULL の意味次第なので、集計の前に決めておきます。NULL と集約関数の関係は SQL の NULL の扱い方 でも詳しく扱っています。

GROUP BY では NULL が1つのグループになる

比較演算子では NULL 同士は等しくなりませんが、GROUP BY では NULL の行は1つのグループにまとめられます。

顧客ごとの集計(ゲスト購入は NULL のグループに入る)
SELECT customer_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
GROUP BY customer_id
ORDER BY customer_id NULLS FIRST;
customer_idcnttotal
NULL22000
124200
21800
322500

ゲスト購入の 104(1500)と 105(500)が、NULL という1つのグループになっています。顧客 3 の合計は、NULL の 107 を飛ばして 2500 です。

該当行がないときの COUNT と SUM

対象の行が1行もないとき、COUNT は 0 を返し、SUM は NULL を返します。

該当行がないときの集計
SELECT COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
WHERE amount > 100000;
cnttotal
0NULL

合計を数値として受け取る処理では、COALESCE(SUM(amount), 0) と書いておくと安全です。

WHERE と HAVING の違い

WHERE と HAVING は、どちらも「条件に合うものだけを残す」句ですが、絞り込む対象と、評価されるタイミングが違います。

WHEREHAVING
絞り込む対象個々の行GROUP BY で作られたグループ
評価されるタイミンググループを作る前グループを作り、集約関数を計算した後
集約関数書けない書ける

PostgreSQL のチュートリアルは、この違いを「WHERE はグループと集約が計算される前に入力行を選び(つまり、どの行が集約の計算に入るかを制御し)、HAVING はグループと集約が計算された後にグループ行を選ぶ」と説明しています。

WHERE - 集計に入れる行を選ぶ

雑貨の売上だけを店舗ごとに合計します。

WHERE で雑貨の行だけを集計する
SELECT shop_id, SUM(amount) AS total
FROM sales
WHERE category = '雑貨'
GROUP BY shop_id
ORDER BY shop_id;
shop_idtotal
14500
22500

食品の行は、グループを作る前に捨てられています。

HAVING - 集計した結果でグループを選ぶ

売上合計が 5000 以上の店舗だけを残します。

HAVING で合計 5000 以上の店舗を残す
SELECT shop_id, SUM(amount) AS total
FROM sales
GROUP BY shop_id
HAVING SUM(amount) >= 5000
ORDER BY shop_id;
shop_idtotal
16500

同じ条件を WHERE に書くとエラーになります。

WHERE に集約関数を書く(エラー)
SELECT shop_id, SUM(amount) AS total
FROM sales
WHERE SUM(amount) >= 5000
GROUP BY shop_id;
エラー(PostgreSQL 18.6)
ERROR:  aggregate functions are not allowed in WHERE
エラー(SQLite 3.54.0)
misuse of aggregate: SUM()

PostgreSQL のチュートリアルは、この制限の理由を「WHERE 句はどの行を集約の計算に含めるかを決めるものなので、集約関数が計算される前に評価されなければならない」と説明しています。合計を計算する前に「合計が 5000 以上か」は判定できない、ということです。

同じ数値の条件でも意味が変わる

WHERE と HAVING のどちらに書くかで、結果の意味がまったく変わる例を見てみます。

WHERE: 1000 円以上の売上を店舗ごとに数える
SELECT shop_id, COUNT(*) AS cnt
FROM sales
WHERE amount >= 1000
GROUP BY shop_id
ORDER BY shop_id;
shop_idcnt
13
21

こちらは「1000 円以上の売上が、各店舗に何件あるか」です。A店は 1200・3000・1500 の3件、B店は 2500 の1件です。

HAVING: すべての売上が 1000 円以上の店舗を探す
SELECT shop_id, COUNT(*) AS cnt
FROM sales
GROUP BY shop_id
HAVING MIN(amount) >= 1000
ORDER BY shop_id;

結果は 0 行です。こちらは「最小の売上が 1000 円以上の店舗」、つまり「すべての売上が 1000 円以上の店舗」を探しています。A店には 800、B店には 500 の売上があるので、どちらも条件を満たしません。

WHERE は「どの行を集計に入れるか」、HAVING は「集計したグループのうちどれを残すか」と覚えておくと、どちらに書くべきか迷いにくくなります。

HAVING に集約関数を使わない条件を書いた場合

HAVING には、グループ化した列だけを使う条件も書けます。

グループ化した列の条件を HAVING に書く
SELECT category, COUNT(*) AS cnt
FROM sales
GROUP BY category
HAVING category = '雑貨';
categorycnt
雑貨4

結果は WHERE category = '雑貨' と書いた場合と同じですが、PostgreSQL のチュートリアルは、集約を必要としない条件は WHERE に書くほうが効率的だと説明しています。WHERE で落とした行は、グループ化と集約の計算をしなくて済むからです。また、HAVING に集約関数を使わない条件を書くことは許されているがあまり役に立たず、同じ条件は WHERE の段階でより効率的に使える、とも書いています。

NOTE

手元の PostgreSQL 18.6 では、上のクエリの EXPLAIN の出力が WHERE に書いた場合と同じになり、category = '雑貨' がテーブルの読み取り時の Filter として適用されていました。プランナが書き換えてくれる場合もあるということですが、すべての条件・すべての製品で同じ最適化が行われるかは確認していません。集約を使わない条件は WHERE に書くのが基本です。

GROUP BY のない HAVING

HAVING は GROUP BY なしでも書けます。PostgreSQL の SELECT のドキュメントによると、HAVING があるとクエリは GROUP BY がなくてもグループ化されたクエリになり、選ばれた行全体が1つのグループとして扱われます。条件が真なら1行、真でなければ 0 行を返します。

GROUP BY のない HAVING
SELECT COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
HAVING COUNT(*) > 100;

結果は 0 行です(全体の件数は 7 なので条件を満たしません)。

論理的な処理順序 - FROM から ORDER BY まで

SQL は SELECT から書き始めますが、評価される順番は書く順番とは違います。PostgreSQL の SELECT のドキュメントには、SELECT 文の処理の流れが番号付きで書かれています。GROUP BY に関係する部分を抜き出すと、次の順番です。

SELECT 文の論理的な処理順序
1. FROM       テーブルを読む(複数あれば結合する)
2. WHERE      条件に合わない行を捨てる
3. GROUP BY   行をグループにまとめ、集約関数を計算する
4. HAVING     条件に合わないグループを捨てる
5. SELECT     出力する列(式)を計算する
6. DISTINCT   重複した行を除く
7. ORDER BY   並べ替える
8. LIMIT      返す行数を絞る

この順番は論理的なものです。実際にデータベースがこの順番どおりに処理するとは限らず、結果が同じになる範囲でプランナが処理を入れ替えます(前節の HAVING の条件が WHERE と同じ位置で適用されていたのがその例です)。それでも、クエリの意味はこの順番で考えると正しく理解できます。

この順番から、次のことが説明できます。

  • WHERE に集約関数を書けないのは、WHERE の時点ではまだグループも集約値も存在しないから
  • HAVING に集約関数を書けるのは、GROUP BY で集約値を計算した後に評価されるから
  • ORDER BY で SELECT の別名を使えるのは、SELECT の後に評価されるから

SELECT の別名をどこで使えるか

最後の点は、SELECT で付けた別名(AS total など)をどの句で参照できるか、という問題につながります。論理的な順番どおりなら、SELECT より前に評価される WHERE・GROUP BY・HAVING では別名は使えないはずです。ところが実際には、製品ごとに独自の拡張があります。

句PostgreSQL 18.6MySQL 8.4SQLite 3.54.0
ORDER BY total使える未確認使える
GROUP BY 別名使える使える(拡張)使える
HAVING total >= 5000エラー使える(拡張)使える
WHERE 別名 > 3000エラードキュメント未確認使えた

根拠は次のとおりです。

  • PostgreSQL: SELECT のドキュメントによると、GROUP BY の要素には入力列の名前、出力列(SELECT リストの項目)の名前か番号、入力列の値から作る任意の式が書ける。HAVING total >= 5000 は手元で column "total" does not exist のエラーになった
  • MySQL: GROUP BY の扱いのページに、標準 SQL は GROUP BY での別名を認めていないが MySQL は拡張して認めていること、HAVING 句で集約列の別名を使えるようにしていることが書かれている(ORDER BY での別名は今回参照したページでは確認していない)
  • SQLite: 手元の 3.54.0 で、GROUP BY・HAVING・WHERE のいずれでも別名が通った。ドキュメントでの位置付けは確認していない
PostgreSQL でエラーになる書き方と、通る書き方
-- エラー: HAVING で SELECT の別名は参照できない
SELECT shop_id, SUM(amount) AS total
FROM sales
GROUP BY shop_id
HAVING total >= 5000;
 
-- 通る: 集約式をもう一度書く
SELECT shop_id, SUM(amount) AS total
FROM sales
GROUP BY shop_id
HAVING SUM(amount) >= 5000;

製品をまたいで動かす SQL では、HAVING には集約式をそのまま書き、GROUP BY には元の列名か式を書くのが無難です。集約式が長くて読みにくい場合は、集計をサブクエリや WITH 句(CTE) に切り出し、外側の WHERE で別名を使って絞り込む方法もあります。

集計を WITH 句に切り出してから絞り込む
WITH shop_totals AS (
  SELECT shop_id, SUM(amount) AS total
  FROM sales
  GROUP BY shop_id
)
SELECT shop_id, total
FROM shop_totals
WHERE total >= 5000;
shop_idtotal
16500

NOTE

PostgreSQL のドキュメントには、GROUP BY の名前があいまいな場合(入力列と出力列の別名が同じ名前の場合)は、出力列の名前ではなく入力列の名前として解釈される、という規則も書かれています。別名を元のテーブルの列名と同じにすると、意図と違う列でグループ化されることがあるので注意が必要です。

SELECT に書ける列の制約

GROUP BY を使ったクエリの SELECT には、どんな列でも書けるわけではありません。店舗ごとに集計しているのに、category をそのまま SELECT に書いてみます。

GROUP BY していない列を SELECT に書く
SELECT shop_id, category, SUM(amount) AS total
FROM sales
GROUP BY shop_id;

PostgreSQL ではエラーになります。

エラー(PostgreSQL 18.6)
ERROR:  column "sales.category" must appear in the GROUP BY clause or be used in an aggregate function

A店のグループには「食品」と「雑貨」の行が両方あるので、1行にまとめたときに category に何を出すべきかが決まらないからです。PostgreSQL のドキュメントは、GROUP BY があるか集約関数がある場合、SELECT リストの式が GROUP BY されていない列を参照できるのは、集約関数の中か、その列がグループ化した列に関数従属している場合だけで、そうでなければ返すべき値が複数ありうるから、と説明しています。

直し方は、意図に応じて次のどれかです。

  • カテゴリごとにも分けたいなら、GROUP BY shop_id, category にする
  • 代表値が1つあればよいなら、MIN(category) や MAX(category) のように集約関数で包む
  • 一覧の文字列にしたいなら、string_agg(PostgreSQL)や GROUP_CONCAT(MySQL・SQLite)などの文字列集約関数を使う

PostgreSQL - 主キーによる関数従属なら許される

PostgreSQL は、グループ化した列がテーブルの主キーであれば、そのテーブルの他の列を GROUP BY に書かなくても SELECT できます。ドキュメントは、グループ化した列(またはその一部)が、グループ化していない列を含むテーブルの主キーであれば関数従属が成り立つ、と書いています。

主キーで GROUP BY すれば name も SELECT できる(PostgreSQL)
SELECT sh.id, sh.name, SUM(s.amount) AS total
FROM sales s
JOIN shops sh ON sh.id = s.shop_id
GROUP BY sh.id
ORDER BY sh.id;
idnametotal
1A店6500
2B店3000

shops.id は主キーなので、id が決まれば name も1つに決まります。ところが、同じ意味のクエリでも GROUP BY s.shop_id と書くとエラーになります。

外部キー側で GROUP BY すると name は SELECT できない(PostgreSQL)
SELECT s.shop_id, sh.name, SUM(s.amount) AS total
FROM sales s
JOIN shops sh ON sh.id = s.shop_id
GROUP BY s.shop_id;
エラー(PostgreSQL 18.6)
ERROR:  column "sh.name" must appear in the GROUP BY clause or be used in an aggregate function

結合条件から見れば s.shop_id と sh.id は同じ値ですが、PostgreSQL が関数従属として認めるのは「グループ化した列が、その列を持つテーブルの主キーである」場合です。s.shop_id は sales の主キーではないので、shops.name との関係は認められません。迷ったら GROUP BY sh.id, sh.name と両方書いておけば、どの製品でも通ります。

MySQL - ONLY_FULL_GROUP_BY

MySQL 8.4 では、SQL モード ONLY_FULL_GROUP_BY が既定で有効です。リファレンスマニュアルは、このモードが有効な場合、SELECT リスト・HAVING 条件・ORDER BY リストが、GROUP BY に書かれておらず関数従属もしていない非集約列を参照するクエリを拒否する、と説明しています。エラーは次のような形です(マニュアルに載っている例)。

ONLY_FULL_GROUP_BY のエラー(MySQL 8.4 リファレンスマニュアルの例)
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by

MySQL も関数従属を検出します。マニュアルによると、グループ化した列が主キーか、NOT NULL の一意列であれば、他の列が関数従属していると認識します。

データの性質上、グループ内で値が1つに決まると分かっているが、MySQL にはそれを判断できない場合は、ANY_VALUE() 関数で包むとクエリが通ります。

ANY_VALUE で包む(MySQL)
SELECT shop_id, ANY_VALUE(category) AS category, SUM(amount) AS total
FROM sales
GROUP BY shop_id;

ただし、ANY_VALUE() は「グループ内のどれか1つの値」を返すだけで、どの値が返るかは決まっていません。今回のデータのように、グループ内で値が本当に複数ある列に使うと、意味のない結果になります。

ONLY_FULL_GROUP_BY を無効にすると、古い MySQL のように非集約列をそのまま書けるようになります。しかしマニュアルは、その場合サーバーは各グループから任意の値を選べるので、値が同じでない限り選ばれる値は非決定的になり、ORDER BY を足しても選ばれる値には影響しない、と注意しています。古いアプリケーションの移行で「エラーが出るからモードを外す」という対処は、結果が不定になる問題を隠すだけなので避けたほうが安全です。

SQLite - bare column と MIN・MAX の特別扱い

SQLite は、GROUP BY していない列を SELECT に書いてもエラーにしません。冒頭のクエリは SQLite 3.54.0 では次の結果を返しました。

shop_idcategorytotal
1食品6500
2食品3000

A店にもB店にも雑貨の売上があるのに、category は「食品」と出ています。SQLite のドキュメントはこのような列を bare column(集約されていない列)と呼び、その値はグループを構成する入力行のどれか1行の値になるが、どの行が使われるかは通常わからないので、多くの場合その値は未定義だ、と説明しています。

例外として、クエリに min() か max() がちょうど1つだけある場合は、bare column の値は最小値または最大値を持つ行から取られます。

max() と bare column(SQLite)
SELECT shop_id, MAX(amount) AS max_amount, id, category
FROM sales
GROUP BY shop_id
ORDER BY shop_id;
shop_idmax_amountidcategory
13000103雑貨
22500106雑貨

「各店舗で最も高い売上の行」が取れていますが、これは SQLite 独自の挙動です。ドキュメントによると、最大値・最小値が複数の行で同じ場合はどの行から取られるかは任意で、min() や max() が2つ以上ある場合もどれが使われるかは任意です。同じクエリは PostgreSQL では column "sales.id" must appear in the GROUP BY clause のエラーになります。「グループごとに最大の行を取る」処理を移植性のある形で書くなら、ウィンドウ関数の ROW_NUMBER() を使います。

LEFT JOIN と組み合わせる - 0 件のグループを出す

最初の節で見たとおり、sales だけを GROUP BY しても、売上のない C店は出てきません。店舗ごとの件数を、売上 0 件の店舗も含めて出すには、店舗テーブルを起点に LEFT JOIN してから集計します。

店舗を起点に LEFT JOIN して集計する
SELECT sh.id, sh.name,
       COUNT(*)                   AS cnt_star,
       COUNT(s.id)                AS cnt_sales,
       COALESCE(SUM(s.amount), 0) AS total
FROM shops sh
LEFT JOIN sales s ON s.shop_id = sh.id
GROUP BY sh.id, sh.name
ORDER BY sh.id;
idnamecnt_starcnt_salestotal
1A店446500
2B店333000
3C店100

C店が出てきましたが、COUNT(*) は 1 になっています。LEFT JOIN は相手のいない C店の行を、sales 側の列をすべて NULL にした1行として残すため、COUNT(*) はその1行を数えてしまうのです。外部結合した側の件数は、COUNT(*) ではなく COUNT(子テーブルの列) で数えます。主キーのように NULL にならない列(ここでは s.id)を選ぶと確実です。合計も、相手がいなければ NULL になるので COALESCE で 0 にしています。

JOIN してから集計するときは、もう1つ注意があります。1対多の関係にあるテーブルを2つ以上つなぐと、行が掛け算で増えて SUM が膨らみます。対処法(結合する前に集計する)は SQL の JOIN の種類と違い で扱っています。

条件付き集計 - FILTER 句と CASE 式

「店舗ごとに、食品の件数と、食品・雑貨それぞれの売上合計を1行に並べたい」というように、条件ごとに別々の集計を横に並べることがよくあります。WHERE で絞ると全部の集約関数に効いてしまうので、集約関数ごとに条件を付ける書き方を使います。

FILTER 句(PostgreSQL・SQLite)

標準 SQL の FILTER 句を使うと、集約関数ごとに入力行を絞れます。

FILTER 句で条件付き集計
SELECT shop_id,
       COUNT(*)                                   AS cnt,
       COUNT(*)    FILTER (WHERE category = '食品') AS food_cnt,
       SUM(amount) FILTER (WHERE category = '食品') AS food_total,
       SUM(amount) FILTER (WHERE category = '雑貨') AS goods_total
FROM sales
GROUP BY shop_id
ORDER BY shop_id;
shop_idcntfood_cntfood_totalgoods_total
14220004500
2315002500

PostgreSQL のチュートリアルは、FILTER は WHERE によく似ているが、行を取り除くのはそれが付いている集約関数の入力からだけだ、と説明しています。上の例でも、cnt は全件を数えたまま、food_cnt だけが食品の行に絞られています。

SQLite は 3.30.0(2019-10-04)で集約関数の FILTER 句に対応しました。MySQL 8.4 については、リファレンスマニュアルの集約関数のページで FILTER 句の記載を確認できませんでした。

CASE 式(どの製品でも使える書き方)

FILTER が使えない製品でも、集約関数の中に CASE 式を書けば同じ集計ができます。

CASE 式で条件付き集計
SELECT shop_id,
       SUM(CASE WHEN category = '食品' THEN 1 ELSE 0 END)  AS food_cnt,
       SUM(CASE WHEN category = '食品' THEN amount END)    AS food_total,
       SUM(CASE WHEN category = '雑貨' THEN amount ELSE 0 END) AS goods_total
FROM sales
GROUP BY shop_id
ORDER BY shop_id;
shop_idfood_cntfood_totalgoods_total
1220004500
215002500

CASE WHEN 条件 THEN amount END は、条件に合わない行で NULL を返します(ELSE を省略すると NULL)。SUM は NULL を飛ばすので、条件に合う行の金額だけが合計されます。件数を数える場合は、THEN 1 ELSE 0 を SUM するか、COUNT(CASE WHEN 条件 THEN 1 END) と書きます。

条件に合う行が1件もないグループでは、FILTER でも ELSE を書かない CASE でも、SUM の結果は NULL になります(COUNT は 0)。0 を出したい場合は ELSE 0 を書くか、COALESCE で包みます。

ROLLUP・GROUPING SETS・CUBE - 小計と総計をまとめて出す

「店舗 x カテゴリの集計に、店舗ごとの小計と全体の総計も付けたい」という場面では、ROLLUP が使えます。

ROLLUP

ROLLUP で小計と総計を付ける(PostgreSQL)
SELECT shop_id, category, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (shop_id, category)
ORDER BY shop_id, category;
shop_idcategorytotal
1雑貨4500
1食品2000
1NULL6500
2雑貨2500
2食品500
2NULL3000
NULLNULL9500

category が NULL の行が店舗ごとの小計、両方が NULL の行が総計です。PostgreSQL のドキュメントによると、ROLLUP ( e1, e2, e3, ... ) は、与えた式のリストと、空のリストを含むその先頭部分すべてを表します。2列なら次の3つのグループ化をまとめて行うのと同じです。

ROLLUP (shop_id, category) と同じ意味の GROUPING SETS
GROUP BY GROUPING SETS ((shop_id, category), (shop_id), ())

() は「何でもグループ化しない」、つまり全体の集計です。ドキュメントは、グループ化セットに含まれない列は、その結果行では NULL に置き換えられる、としています。

GROUPING SETS

GROUPING SETS を直接書くと、必要なグループ化の組み合わせだけを指定できます。店舗別の合計、カテゴリ別の合計、総計の3種類を1つのクエリで出してみます。

GROUPING SETS で必要な集計だけを出す(PostgreSQL)
SELECT shop_id, category, SUM(amount) AS total,
       GROUPING(shop_id, category) AS g
FROM sales
GROUP BY GROUPING SETS ((shop_id), (category), ())
ORDER BY g, shop_id, category;
shop_idcategorytotalg
1NULL65001
2NULL30001
NULL雑貨70002
NULL食品25002
NULLNULL95003

SELECT のドキュメントは、GROUPING SETS などがあると、GROUP BY 句全体がいくつかの独立したグループ化セットを定義し、その効果は個々のグループ化セットを GROUP BY に書いたサブクエリを UNION ALL でつないだものと同じだ、と説明しています。

GROUPING() 関数は、その行でその列が集計の対象外(小計・総計のために NULL にされた)なら 1、そうでなければ 0 を返します。PostgreSQL の集約関数のドキュメントによると、複数の列を渡すと各列の結果をビットとして並べた整数(ビットマスク)になり、右端の引数が最下位ビットに対応します。上の例では左の shop_id が上位ビット、右の category が下位ビットです。category だけが集計対象外の行は 1、shop_id だけが対象外の行は 2、両方が対象外の総計行は 3 になっています。

GROUPING() で小計の NULL と本物の NULL を区別する

小計・総計の行の NULL は、データに入っている本物の NULL と見分けがつきません。customer_id のように NULL を含む列で ROLLUP すると、「ゲスト購入のグループ」の NULL と「総計」の NULL が同じ見た目になります。そこで GROUPING() を使って、小計・総計の行にラベルを付けます。

GROUPING() でラベルを付ける(PostgreSQL)
SELECT CASE WHEN GROUPING(shop_id) = 1 THEN '全店'
            ELSE CAST(shop_id AS TEXT) END                   AS shop,
       CASE WHEN GROUPING(category) = 1 THEN '小計'
            ELSE category END                                AS category,
       SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (shop_id, category)
ORDER BY GROUPING(shop_id), shop_id, GROUPING(category), category;
shopcategorytotal
1雑貨4500
1食品2000
1小計6500
2雑貨2500
2食品500
2小計3000
全店小計9500

COALESCE(category, '小計') でもラベルは付けられますが、データ自体に NULL がある列では本物の NULL まで「小計」になってしまいます。GROUPING() を使うほうが確実です。

CUBE

CUBE は、与えた列のすべての組み合わせ(べき集合)でグループ化します。PostgreSQL のドキュメントでは、CUBE ( a, b, c ) は GROUPING SETS で8通りの組み合わせを並べたものと同じだと示されています。2列なら4通りです。

CUBE ですべての組み合わせを集計する(PostgreSQL)
SELECT shop_id, category, SUM(amount) AS total,
       GROUPING(shop_id) AS g_shop, GROUPING(category) AS g_cat
FROM sales
GROUP BY CUBE (shop_id, category)
ORDER BY g_shop, g_cat, shop_id, category;
shop_idcategorytotalg_shopg_cat
1雑貨450000
1食品200000
2雑貨250000
2食品50000
1NULL650001
2NULL300001
NULL雑貨700010
NULL食品250010
NULLNULL950011

店舗 x カテゴリ、店舗別、カテゴリ別、総計の4種類の集計が1回で出ています。クロス集計表の縦横の合計を作るときに便利です。

MySQL の WITH ROLLUP

MySQL 8.4 のリファレンスマニュアルでは、GROUP BY の修飾子として WITH ROLLUP が説明されています。別の書き方として GROUP BY ROLLUP (year) の形も同じページに載っています。

WITH ROLLUP(MySQL)
SELECT shop_id, category, SUM(amount) AS total
FROM sales
GROUP BY shop_id, category WITH ROLLUP;

マニュアルに書かれている主な注意点は次のとおりです。

  • 小計・総計の行の NULL は、行がクライアントへ送られる時点で作られる。そのため NULL として判定できるのは SELECT リストか HAVING 句だけで、WHERE 句や結合条件では判定できない
  • 小計の NULL と通常の NULL を区別するには GROUPING() を使う。GROUPING() は SELECT リスト・HAVING 句・ORDER BY 句で使える
  • LIMIT は ROLLUP で追加された行も含めて数えるので、結果が解釈しにくくなることがある

MySQL の GROUP BY 修飾子のページで扱われているのは ROLLUP だけで、GROUPING SETS と CUBE の記載はありませんでした。MySQL でのこれらの対応状況は未確認です。

SQLite では UNION ALL で代用する

SQLite 3.54.0 で GROUP BY ROLLUP (...) を実行すると no such function: ROLLUP、GROUPING SETS は構文エラーになりました。小計と総計が必要なら、PostgreSQL のドキュメントが説明する「UNION ALL と同じ」という定義どおりに、集計を UNION ALL でつなぎます。

UNION ALL で ROLLUP を代用する(SQLite)
SELECT shop_id, category, total FROM (
  SELECT shop_id, category, SUM(amount) AS total FROM sales GROUP BY shop_id, category
  UNION ALL
  SELECT shop_id, NULL, SUM(amount) FROM sales GROUP BY shop_id
  UNION ALL
  SELECT NULL, NULL, SUM(amount) FROM sales
)
ORDER BY shop_id IS NULL, shop_id, category IS NULL, category;
shop_idcategorytotal
1雑貨4500
1食品2000
1NULL6500
2雑貨2500
2食品500
2NULL3000
NULLNULL9500

ROLLUP と同じ結果になりました。ただしテーブルを3回読むことになるので、件数が多い場合は小計の計算をアプリケーション側で行うことも検討します。

ウィンドウ関数との違い - 行を残すか、まとめるか

GROUP BY とよく比べられるのが、SUM(...) OVER (PARTITION BY ...) のようなウィンドウ関数です。どちらも「グループごとに集計する」点は同じですが、結果の行数が違います。

ウィンドウ関数で店舗の合計を各行に並べる
SELECT id, shop_id, amount,
       SUM(amount) OVER (PARTITION BY shop_id) AS shop_total
FROM sales
ORDER BY id;
idshop_idamountshop_total
101112006500
10218006500
103130006500
104115006500
10525003000
106225003000
1072NULL3000

GROUP BY shop_id は7行を2行にまとめましたが、ウィンドウ関数は7行を残したまま、各行の横に店舗の合計を付けています。PostgreSQL のドキュメントによると、ウィンドウ関数はグループ化・集約・HAVING による絞り込みがすべて済んだ後に評価されます。

GROUP BYウィンドウ関数
結果の行数グループの数に減る元の行数のまま
明細の列グループ化した列と集約値だけすべての列をそのまま出せる
向いている用途集計表、件数・合計の一覧明細に合計・構成比・順位・累計を並べる
評価されるタイミングWHERE の後GROUP BY・HAVING の後

「各売上が店舗の売上の何 % か」のように、明細と集計値を同じ行で使いたい場合はウィンドウ関数が向いています。GROUP BY で集計した結果に、さらにウィンドウ関数で順位を付けることもできます。詳しくは SQL ウィンドウ関数入門 を参照してください。

よくあるエラーと対処

ここまでに出てきたエラーを、PostgreSQL 18.6 と SQLite 3.54.0 の出力でまとめます。

書いたクエリPostgreSQL 18.6SQLite 3.54.0対処
WHERE SUM(amount) >= 5000aggregate functions are not allowed in WHEREmisuse of aggregate: SUM()条件を HAVING に移す
GROUP BY していない列を SELECTmust appear in the GROUP BY clause or be used in an aggregate functionエラーにならず、どれか1行の値が出るGROUP BY に足すか集約関数で包む
GROUP BY なしで SELECT shop_id, COUNT(*)同上エラーにならず 1 行返るGROUP BY shop_id を足す
MAX(SUM(amount))aggregate function calls cannot be nestedmisuse of aggregate function SUM()サブクエリで集計してから外側で MAX
HAVING total >= 5000(別名)column "total" does not exist通るHAVING SUM(amount) >= 5000 と書く

MySQL では、GROUP BY していない列を SELECT すると ONLY_FULL_GROUP_BY のエラー(ERROR 1055)になります。また、GROUP BY なしで集約関数と非集約列を混ぜると ERROR 1140(In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ...)になることがマニュアルに載っています。

集約関数を入れ子にしたい場合(「店舗ごとの合計のうち最大のもの」など)は、内側で集計した結果をサブクエリにして、外側でもう一度集約します。

集計結果をもう一度集計する
SELECT MAX(total) AS max_total
FROM (
  SELECT shop_id, SUM(amount) AS total
  FROM sales
  GROUP BY shop_id
) t;
max_total
6500

SQLite はエラーにならない書き方が多い分、間違いに気付きにくい点に注意が必要です。SQLite で書いたクエリを PostgreSQL や MySQL へ移すときに、初めてエラーになることがあります。

パフォーマンス - 集計する行を減らす

GROUP BY の処理コストは、おおむねグループ化の対象になる行の数で決まります。性能を考えるうえでの基本は次の3点です。

1. 集約を使わない条件は WHERE で先に絞る

前述のとおり、PostgreSQL のチュートリアルは、集約を必要としない条件は HAVING より WHERE に書くほうが効率的だとしています。WHERE で落とした行は、グループ化と集約の計算をしなくて済むからです。

2. WHERE の条件にはインデックスが効く

WHERE の条件は、テーブルを読む段階で使われるので、インデックスで読む行そのものを減らせます。手元の PostgreSQL 18.6 で、20 万行のテーブル big の sold_on(日付)列にインデックスを作り、直近1週間の売上を店舗ごとに集計したときの実行計画です。

直近1週間を店舗ごとに集計する
EXPLAIN (COSTS OFF)
SELECT shop_id, SUM(amount)
FROM big
WHERE sold_on >= DATE '2026-11-11'
GROUP BY shop_id;
実行計画(PostgreSQL 18.6)
 HashAggregate
   Group Key: shop_id
   ->  Bitmap Heap Scan on big
         Recheck Cond: (sold_on >= '2026-11-11'::date)
         ->  Bitmap Index Scan on big_sold_on
               Index Cond: (sold_on >= '2026-11-11'::date)

インデックス(big_sold_on)で該当する約 4400 行だけを読み、それをハッシュ表でグループにまとめています(HashAggregate)。

一方、集約値に対する HAVING の条件は、グループを作り終えた後でしか評価できません。

HAVING で集約値を絞る
EXPLAIN (COSTS OFF)
SELECT shop_id, SUM(amount)
FROM big
GROUP BY shop_id
HAVING SUM(amount) > 100;
実行計画(PostgreSQL 18.6)
 Finalize GroupAggregate
   Group Key: shop_id
   Filter: (sum(amount) > 100)
   ->  Gather Merge
         Workers Planned: 1
         ->  Sort
               Sort Key: shop_id
               ->  Partial HashAggregate
                     Group Key: shop_id
                     ->  Parallel Seq Scan on big

テーブル全体を読んで(Parallel Seq Scan)グループ化した後に、Filter として HAVING の条件が適用されています。HAVING の条件は、インデックスで読む行を減らすのには使えません。

グループ化の方式(ハッシュでまとめるか、並べ替えてからまとめるか)や並列化は、プランナが行数の見積もりから選びます。実行計画の読み方は クエリ実行計画入門 で、インデックスが効く条件・効かない条件は データベースインデックス入門 で扱っています。

3. 集計はデータベースに任せる

アプリケーション側でグループごとにクエリを発行して件数や合計を取ると、グループの数だけクエリが走ります。ORM で一覧を出すときに起きやすい N+1 問題 と同じ構造です。グループごとの件数や合計は、GROUP BY を使って1回のクエリで取るほうが、通信の回数もデータベースの負荷も少なく済みます。

まとめ

  • GROUP BY は、指定した列の値が同じ行を1つのグループにまとめ、グループごとに1行を返す。グループは実在する値からしか作られないので、0 件のグループを出すには親テーブルから LEFT JOIN する。
  • COUNT(*) は行を数え、COUNT(列) は NULL ではない値を数え、COUNT(DISTINCT 列) は値の種類を数える。SUM・AVG・MIN・MAX は NULL を飛ばすので、AVG の分母には NULL の行が入らない。
  • WHERE はグループを作る前に行を絞り、HAVING はグループを作った後にグループを絞る。集約関数を使う条件は HAVING に、それ以外は WHERE に書く。
  • SQL の論理的な処理順序は FROM、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT。WHERE に集約関数が書けない理由も、ORDER BY で別名が使える理由も、この順番で説明できる。
  • SELECT の別名を GROUP BY や HAVING で使えるかは製品で違う。PostgreSQL は GROUP BY では使えるが HAVING では使えない。移植性を考えるなら HAVING には集約式をそのまま書く。
  • SELECT に書ける非集約列は、原則として GROUP BY に書いた列だけ。PostgreSQL と MySQL は主キーによる関数従属を認める。MySQL 8.4 は ONLY_FULL_GROUP_BY が既定で有効で、SQLite はエラーにせず不定の値を返す。
  • 条件ごとの集計は、PostgreSQL と SQLite なら FILTER (WHERE ...)、どの製品でも使える書き方なら SUM(CASE WHEN ... END)。
  • 小計と総計は ROLLUP、必要な組み合わせだけなら GROUPING SETS、すべての組み合わせなら CUBE。小計の NULL と本物の NULL は GROUPING() で区別する。MySQL は WITH ROLLUP、SQLite は UNION ALL で代用する。
  • 明細の行を残したまま集計値を並べたいなら、GROUP BY ではなくウィンドウ関数を使う。
  • 性能の基本は、集計の対象になる行を WHERE とインデックスで減らすこと。

集計のクエリが思った結果にならないときは、まず GROUP BY と集約関数を外して、WHERE を通った明細の行を確かめてみてください。どの行がどのグループに入っているかが見えると、WHERE と HAVING のどちらに条件を書くべきか、NULL の行がどう扱われているかが判断しやすくなります。

参考リンク

関連記事

SQL の UPSERT - ON CONFLICT・ON DUPLICATE KEY UPDATE・MERGE の書き方と同時実行の注意

SQL の UPSERT - ON CONFLICT・ON DUPLICATE KEY UPDATE・MERGE の書き方と同時実行の注意

約73分

「あれば更新、なければ挿入」を SQL で書く UPSERT を、同じ小さなサンプルデータに対する実行結果を並べて整理します。SELECT してから INSERT・UPDATE する書き方が同時実行で壊れる理由とユニーク制約が必要な理由、PostgreSQL の INSERT ... ON CONFLICT(DO NOTHING・DO UPDATE・EXCLUDED・ON CONSTRAINT・WHERE 付きの更新・部分インデックス・RETURNING と PostgreSQL 18 の OLD / NEW)、PostgreSQL 15 の MERGE と 17 で入った RETURNING・merge_action()・WHEN NOT MATCHED BY SOURCE、MySQL の INSERT ... ON DUPLICATE KEY UPDATE(非推奨の VALUES() と行エイリアス AS new、affected rows が 1・2・0 になる挙動、代入の順番、複数のユニークキーで別の行が更新される問題、AUTO_INCREMENT の欠番)、REPLACE と INSERT IGNORE の危険性、SQLite の ON CONFLICT と INSERT OR REPLACE の違い、SQL Server の MERGE と HOLDLOCK、2つのセッションを同時に動かしたときの挙動、1文の中で同じキーを2回更新したときのエラー、NULL がユニーク制約で重複扱いにならない問題と NULLS NOT DISTINCT、DO NOTHING で既存の行が返らない問題と回避策、一括 UPSERT、よくあるエラーまでを、PostgreSQL 18.6・MySQL 8.4.13・SQLite 3.54.0 の実行結果と各公式ドキュメントをもとにまとめます。

SQL の CASE 式の使い方 - 検索 CASE と単純 CASE、条件付き集計・並べ替え・UPDATE と評価順序の注意

SQL の CASE 式の使い方 - 検索 CASE と単純 CASE、条件付き集計・並べ替え・UPDATE と評価順序の注意

約68分

SQL の CASE 式を、同じ小さなサンプルデータに対する実行結果を並べて整理します。単純 CASE と検索 CASE、ELSE を省略すると NULL になること、上から順に評価して最初に一致した分岐を返すこと、WHEN NULL が一致しない理由、SELECT・WHERE・GROUP BY・HAVING・ORDER BY・UPDATE など書ける場所、年齢層・金額帯の区分け、SUM(CASE ...)・COUNT(CASE ...) の条件付き集計とピボット、FILTER 句との比較、任意の並び順、UPDATE で複数の値を1文で書き換える方法と ELSE 漏れの事故、NULLIF による 0 除算の回避、COALESCE・GREATEST・LEAST・MySQL の IF()・SQLite の iif() との関係、結果の型の決まり方とエラー、短絡評価が効かない場面(定数式の早期評価と集約関数)、WHERE の CASE とインデックス、ストアドプロシージャの CASE 文との違いまでを、PostgreSQL 18.6・MySQL 8.4.13・SQLite 3.54.0 の実行結果と各公式ドキュメントをもとにまとめます。

SQL のサブクエリの使い方 - IN・EXISTS・相関サブクエリ・LATERAL と NOT IN の落とし穴

SQL のサブクエリの使い方 - IN・EXISTS・相関サブクエリ・LATERAL と NOT IN の落とし穴

約58分

SQL のサブクエリ(副問い合わせ)を、同じ小さなサンプルデータに対する実行結果を並べて整理します。スカラーサブクエリと 0 行・複数行のときの挙動、IN と JOIN の違い、EXISTS と NOT EXISTS、NOT IN に NULL が混ざると 0 行になる罠と回避方法、相関サブクエリ、FROM 句のサブクエリ(派生テーブル)と別名の要否、ANY・SOME・ALL と空集合、LATERAL と LEFT JOIN LATERAL、JOIN・CTE との使い分け、UPDATE・DELETE での注意、セミジョイン・アンチジョインへの変換と PostgreSQL の EXPLAIN での見え方、MySQL 8.4 の semijoin・antijoin 最適化、よくあるエラーまでを、各公式ドキュメントを一次ソースにまとめます。

楽天市場のおすすめPR / 楽天アフィリエイト