
SQL の GROUP BY と HAVING の使い方 - WHERE との違い・集約関数・論理的な処理順序
当サイトは 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 / CUBE | MySQL は 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 テーブル:
| id | name |
|---|---|
| 1 | A店 |
| 2 | B店 |
| 3 | C店 |
sales テーブル:
| id | shop_id | category | amount | customer_id |
|---|---|---|---|---|
| 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 |
ポイントは次の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_id | cnt | total |
|---|---|---|
| 1 | 4 | 6500 |
| 2 | 3 | 3000 |
7行の明細が、shop_id の値ごとに2行へまとまりました。A店は 1200 + 800 + 3000 + 1500 = 6500、B店は 500 + 2500 = 3000 です(B店の NULL の行は SUM で飛ばされます。後述)。
GROUP BY に複数の列を並べると、値の組み合わせごとにグループができます。
SELECT shop_id, category, COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
GROUP BY shop_id, category
ORDER BY shop_id, category;| shop_id | category | cnt | total |
|---|---|---|---|
| 1 | 雑貨 | 2 | 4500 |
| 1 | 食品 | 2 | 2000 |
| 2 | 雑貨 | 2 | 2500 |
| 2 | 食品 | 1 | 500 |
ここで、結果に 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_rows | cnt_amount | cnt_customer | distinct_customers | total | avg_amount | min_amount | max_amount |
|---|---|---|---|---|---|---|---|
| 7 | 6 | 5 | 3 | 9500 | 1583.3333333333333 | 500 | 3000 |
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_id | cnt | cnt_amount | total | avg_amount |
|---|---|---|---|---|
| 1 | 4 | 4 | 6500 | 1625.0 |
| 2 | 3 | 2 | 3000 | 1500.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つのグループにまとめられます。
SELECT customer_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
GROUP BY customer_id
ORDER BY customer_id NULLS FIRST;| customer_id | cnt | total |
|---|---|---|
| NULL | 2 | 2000 |
| 1 | 2 | 4200 |
| 2 | 1 | 800 |
| 3 | 2 | 2500 |
ゲスト購入の 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;| cnt | total |
|---|---|
| 0 | NULL |
合計を数値として受け取る処理では、COALESCE(SUM(amount), 0) と書いておくと安全です。
WHERE と HAVING の違い
WHERE と HAVING は、どちらも「条件に合うものだけを残す」句ですが、絞り込む対象と、評価されるタイミングが違います。
| WHERE | HAVING | |
|---|---|---|
| 絞り込む対象 | 個々の行 | GROUP BY で作られたグループ |
| 評価されるタイミング | グループを作る前 | グループを作り、集約関数を計算した後 |
| 集約関数 | 書けない | 書ける |
PostgreSQL のチュートリアルは、この違いを「WHERE はグループと集約が計算される前に入力行を選び(つまり、どの行が集約の計算に入るかを制御し)、HAVING はグループと集約が計算された後にグループ行を選ぶ」と説明しています。
WHERE - 集計に入れる行を選ぶ
雑貨の売上だけを店舗ごとに合計します。
SELECT shop_id, SUM(amount) AS total
FROM sales
WHERE category = '雑貨'
GROUP BY shop_id
ORDER BY shop_id;| shop_id | total |
|---|---|
| 1 | 4500 |
| 2 | 2500 |
食品の行は、グループを作る前に捨てられています。
HAVING - 集計した結果でグループを選ぶ
売上合計が 5000 以上の店舗だけを残します。
SELECT shop_id, SUM(amount) AS total
FROM sales
GROUP BY shop_id
HAVING SUM(amount) >= 5000
ORDER BY shop_id;| shop_id | total |
|---|---|
| 1 | 6500 |
同じ条件を WHERE に書くとエラーになります。
SELECT shop_id, SUM(amount) AS total
FROM sales
WHERE SUM(amount) >= 5000
GROUP BY shop_id;ERROR: aggregate functions are not allowed in WHEREmisuse of aggregate: SUM()PostgreSQL のチュートリアルは、この制限の理由を「WHERE 句はどの行を集約の計算に含めるかを決めるものなので、集約関数が計算される前に評価されなければならない」と説明しています。合計を計算する前に「合計が 5000 以上か」は判定できない、ということです。
同じ数値の条件でも意味が変わる
WHERE と HAVING のどちらに書くかで、結果の意味がまったく変わる例を見てみます。
SELECT shop_id, COUNT(*) AS cnt
FROM sales
WHERE amount >= 1000
GROUP BY shop_id
ORDER BY shop_id;| shop_id | cnt |
|---|---|
| 1 | 3 |
| 2 | 1 |
こちらは「1000 円以上の売上が、各店舗に何件あるか」です。A店は 1200・3000・1500 の3件、B店は 2500 の1件です。
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 には、グループ化した列だけを使う条件も書けます。
SELECT category, COUNT(*) AS cnt
FROM sales
GROUP BY category
HAVING category = '雑貨';| category | cnt |
|---|---|
| 雑貨 | 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 行を返します。
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 に関係する部分を抜き出すと、次の順番です。
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.6 | MySQL 8.4 | SQLite 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のいずれでも別名が通った。ドキュメントでの位置付けは確認していない
-- エラー: 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 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_id | total |
|---|---|
| 1 | 6500 |
NOTE
PostgreSQL のドキュメントには、GROUP BY の名前があいまいな場合(入力列と出力列の別名が同じ名前の場合)は、出力列の名前ではなく入力列の名前として解釈される、という規則も書かれています。別名を元のテーブルの列名と同じにすると、意図と違う列でグループ化されることがあるので注意が必要です。
SELECT に書ける列の制約
GROUP BY を使ったクエリの SELECT には、どんな列でも書けるわけではありません。店舗ごとに集計しているのに、category をそのまま SELECT に書いてみます。
SELECT shop_id, category, SUM(amount) AS total
FROM sales
GROUP BY shop_id;PostgreSQL ではエラーになります。
ERROR: column "sales.category" must appear in the GROUP BY clause or be used in an aggregate functionA店のグループには「食品」と「雑貨」の行が両方あるので、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 できます。ドキュメントは、グループ化した列(またはその一部)が、グループ化していない列を含むテーブルの主キーであれば関数従属が成り立つ、と書いています。
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;| id | name | total |
|---|---|---|
| 1 | A店 | 6500 |
| 2 | B店 | 3000 |
shops.id は主キーなので、id が決まれば name も1つに決まります。ところが、同じ意味のクエリでも GROUP BY s.shop_id と書くとエラーになります。
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;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 に書かれておらず関数従属もしていない非集約列を参照するクエリを拒否する、と説明しています。エラーは次のような形です(マニュアルに載っている例)。
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_byMySQL も関数従属を検出します。マニュアルによると、グループ化した列が主キーか、NOT NULL の一意列であれば、他の列が関数従属していると認識します。
データの性質上、グループ内で値が1つに決まると分かっているが、MySQL にはそれを判断できない場合は、ANY_VALUE() 関数で包むとクエリが通ります。
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_id | category | total |
|---|---|---|
| 1 | 食品 | 6500 |
| 2 | 食品 | 3000 |
A店にもB店にも雑貨の売上があるのに、category は「食品」と出ています。SQLite のドキュメントはこのような列を bare column(集約されていない列)と呼び、その値はグループを構成する入力行のどれか1行の値になるが、どの行が使われるかは通常わからないので、多くの場合その値は未定義だ、と説明しています。
例外として、クエリに min() か max() がちょうど1つだけある場合は、bare column の値は最小値または最大値を持つ行から取られます。
SELECT shop_id, MAX(amount) AS max_amount, id, category
FROM sales
GROUP BY shop_id
ORDER BY shop_id;| shop_id | max_amount | id | category |
|---|---|---|---|
| 1 | 3000 | 103 | 雑貨 |
| 2 | 2500 | 106 | 雑貨 |
「各店舗で最も高い売上の行」が取れていますが、これは 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 してから集計します。
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;| id | name | cnt_star | cnt_sales | total |
|---|---|---|---|---|
| 1 | A店 | 4 | 4 | 6500 |
| 2 | B店 | 3 | 3 | 3000 |
| 3 | C店 | 1 | 0 | 0 |
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 句を使うと、集約関数ごとに入力行を絞れます。
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_id | cnt | food_cnt | food_total | goods_total |
|---|---|---|---|---|
| 1 | 4 | 2 | 2000 | 4500 |
| 2 | 3 | 1 | 500 | 2500 |
PostgreSQL のチュートリアルは、FILTER は WHERE によく似ているが、行を取り除くのはそれが付いている集約関数の入力からだけだ、と説明しています。上の例でも、cnt は全件を数えたまま、food_cnt だけが食品の行に絞られています。
SQLite は 3.30.0(2019-10-04)で集約関数の FILTER 句に対応しました。MySQL 8.4 については、リファレンスマニュアルの集約関数のページで FILTER 句の記載を確認できませんでした。
CASE 式(どの製品でも使える書き方)
FILTER が使えない製品でも、集約関数の中に 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_id | food_cnt | food_total | goods_total |
|---|---|---|---|
| 1 | 2 | 2000 | 4500 |
| 2 | 1 | 500 | 2500 |
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
SELECT shop_id, category, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (shop_id, category)
ORDER BY shop_id, category;| shop_id | category | total |
|---|---|---|
| 1 | 雑貨 | 4500 |
| 1 | 食品 | 2000 |
| 1 | NULL | 6500 |
| 2 | 雑貨 | 2500 |
| 2 | 食品 | 500 |
| 2 | NULL | 3000 |
| NULL | NULL | 9500 |
category が NULL の行が店舗ごとの小計、両方が NULL の行が総計です。PostgreSQL のドキュメントによると、ROLLUP ( e1, e2, e3, ... ) は、与えた式のリストと、空のリストを含むその先頭部分すべてを表します。2列なら次の3つのグループ化をまとめて行うのと同じです。
GROUP BY GROUPING SETS ((shop_id, category), (shop_id), ())() は「何でもグループ化しない」、つまり全体の集計です。ドキュメントは、グループ化セットに含まれない列は、その結果行では NULL に置き換えられる、としています。
GROUPING SETS
GROUPING SETS を直接書くと、必要なグループ化の組み合わせだけを指定できます。店舗別の合計、カテゴリ別の合計、総計の3種類を1つのクエリで出してみます。
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_id | category | total | g |
|---|---|---|---|
| 1 | NULL | 6500 | 1 |
| 2 | NULL | 3000 | 1 |
| NULL | 雑貨 | 7000 | 2 |
| NULL | 食品 | 2500 | 2 |
| NULL | NULL | 9500 | 3 |
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() を使って、小計・総計の行にラベルを付けます。
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;| shop | category | total |
|---|---|---|
| 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通りです。
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_id | category | total | g_shop | g_cat |
|---|---|---|---|---|
| 1 | 雑貨 | 4500 | 0 | 0 |
| 1 | 食品 | 2000 | 0 | 0 |
| 2 | 雑貨 | 2500 | 0 | 0 |
| 2 | 食品 | 500 | 0 | 0 |
| 1 | NULL | 6500 | 0 | 1 |
| 2 | NULL | 3000 | 0 | 1 |
| NULL | 雑貨 | 7000 | 1 | 0 |
| NULL | 食品 | 2500 | 1 | 0 |
| NULL | NULL | 9500 | 1 | 1 |
店舗 x カテゴリ、店舗別、カテゴリ別、総計の4種類の集計が1回で出ています。クロス集計表の縦横の合計を作るときに便利です。
MySQL の WITH ROLLUP
MySQL 8.4 のリファレンスマニュアルでは、GROUP BY の修飾子として WITH ROLLUP が説明されています。別の書き方として GROUP BY ROLLUP (year) の形も同じページに載っています。
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 でつなぎます。
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_id | category | total |
|---|---|---|
| 1 | 雑貨 | 4500 |
| 1 | 食品 | 2000 |
| 1 | NULL | 6500 |
| 2 | 雑貨 | 2500 |
| 2 | 食品 | 500 |
| 2 | NULL | 3000 |
| NULL | NULL | 9500 |
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;| id | shop_id | amount | shop_total |
|---|---|---|---|
| 101 | 1 | 1200 | 6500 |
| 102 | 1 | 800 | 6500 |
| 103 | 1 | 3000 | 6500 |
| 104 | 1 | 1500 | 6500 |
| 105 | 2 | 500 | 3000 |
| 106 | 2 | 2500 | 3000 |
| 107 | 2 | NULL | 3000 |
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.6 | SQLite 3.54.0 | 対処 |
|---|---|---|---|
WHERE SUM(amount) >= 5000 | aggregate functions are not allowed in WHERE | misuse of aggregate: SUM() | 条件を HAVING に移す |
GROUP BY していない列を SELECT | must 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 nested | misuse 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週間の売上を店舗ごとに集計したときの実行計画です。
EXPLAIN (COSTS OFF)
SELECT shop_id, SUM(amount)
FROM big
WHERE sold_on >= DATE '2026-11-11'
GROUP BY shop_id; 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 の条件は、グループを作り終えた後でしか評価できません。
EXPLAIN (COSTS OFF)
SELECT shop_id, SUM(amount)
FROM big
GROUP BY shop_id
HAVING SUM(amount) > 100; 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 の行がどう扱われているかが判断しやすくなります。
参考リンク
- SELECT(PostgreSQL 公式)
- Table Expressions - GROUP BY and HAVING / GROUPING SETS, CUBE, and ROLLUP(PostgreSQL 公式)
- Aggregate Functions(PostgreSQL 公式チュートリアル)
- Aggregate Functions(PostgreSQL 公式)
- Value Expressions - Aggregate Expressions(PostgreSQL 公式)
- MySQL Handling of GROUP BY(MySQL 8.4 リファレンスマニュアル)
- GROUP BY Modifiers(MySQL 8.4 リファレンスマニュアル)
- Aggregate Function Descriptions(MySQL 8.4 リファレンスマニュアル)
- SELECT(SQLite 公式)
- Built-in Aggregate Functions(SQLite 公式)
- SQLite Release 3.30.0
関連記事
- SQL の JOIN の種類と違い - INNER / LEFT / FULL / CROSS の結果を例で理解する
- SQL ウィンドウ関数入門 - OVER と PARTITION BY、ROW_NUMBER・RANK・LAG の使い分け
- SQL の WITH 句(CTE)入門 - 共通テーブル式の書き方と再帰 CTE で階層データをたどる方法
- SQL の NULL の扱い方 - 3値論理と NOT IN の落とし穴、IS NULL・COALESCE の使い分け
- データベースインデックス入門 - B-treeの仕組みと、効くクエリ・効かないクエリ
- クエリ実行計画入門 - EXPLAINの読み方とプランナがJOINアルゴリズムを選ぶ理由
- N+1問題とは - ORM で起きる原因と Eager Loading・JOIN・バッチ取得での対策


