SQLite GROUP BYとHAVING｜集計後の絞り込みを徹底解説

GROUP BY で行をグループにまとめる

COUNT、SUM、AVG といった集計関数は、たくさんの行を 1 つの数値に集約してくれます。そこに GROUP BY を組み合わせると、カテゴリごと の集計ができるようになります。たとえば顧客ごと、月ごと、ステータスごとに 1 行ずつ、といった具合です。グループ化に使ったカラムの値（複数カラムの組み合わせでも OK）が一意になるたびに、結果セットに 1 行ずつ出てくるイメージですね。

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   50.00),
    ('Ada',   30.00),
    ('Boris', 80.00),
    ('Boris', 20.00),
    ('Boris', 15.00),
    ('Cleo', 200.00);

SELECT customer, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer;

顧客は3人なので、結果も3行。元の6行は消えて、顧客ごとのバケツにまとめられ、それぞれの中で COUNT(*) と SUM(amount) が計算されています。

イメージとしてはこうです。GROUP BY customer は「同じ customer を持つ行をひとつのグループとして扱え」という指示。集計関数はそのグループごとに別々に動きます。

SELECT に書けるものと書けないもの

ここでつまずく人が多いポイントです。GROUP BY を使うときは、SELECT に並べるカラムはすべて、GROUP BY 句に含まれているか、集計関数の中に入っているかのどちらかでなければいけません。そうでないと、グループ内のどの行の値を返せばいいのか決まらず、曖昧になってしまうからです。

CREATE TABLE sales (
    region TEXT,
    rep TEXT,
    amount REAL
);

INSERT INTO sales VALUES
    ('North', 'Ada', 100),
    ('North', 'Boris', 200),
    ('South', 'Cleo', 150);

-- これは動作する: region はグループ化され、amount は集約される。
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;

SELECT region, rep, SUM(amount) を GROUP BY region だけで書いた場合、SQLite はエラーにせずそのまま実行してくれます（他の DB なら弾かれるところを、SQLite は寛容です）。ただし rep は同じグループの中から 適当に 1 件選ばれるだけで、どの担当者の名前が返るかは保証されません。これに頼るのはやめましょう。表示する非集計カラムは、すべて GROUP BY に並べるのが鉄則です。

SQLite HAVING で集計後のグループを絞り込む

WHERE は集計前の行を、HAVING は集計後のグループを絞り込みます。SQLite の WHERE と HAVING の違いはこれが本質で、COUNT(*) > 1 を WHERE 句に書けないのもこのためです。WHERE が評価される時点では、まだ集計結果が存在しないからです。

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada', 50), ('Ada', 30),
    ('Boris', 80), ('Boris', 20), ('Boris', 15),
    ('Cleo', 200);

SELECT customer, COUNT(*) AS order_count
FROM orders
GROUP BY customer
HAVING COUNT(*) > 1;

Cleo は注文が 1 件しかないので、彼女のグループは除外されます。残るのは Ada と Boris です。条件は個々の行ではなく、グループごとの集計値に対して評価される点がポイントです。

SQLite では、SELECT で付けたカラムの別名(エイリアス)を HAVING の中でそのまま参照できます。

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   50.00),
    ('Ada',   30.00),
    ('Boris', 80.00),
    ('Boris', 20.00),
    ('Boris', 15.00),
    ('Cleo', 200.00);

SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING total >= 100;

こちらの方が、HAVING 句の中で SUM(amount) を繰り返すより読みやすくなることが多いです。

SQLite WHERE と HAVING の違いと併用パターン

WHERE と HAVING はどちらか一方を選ぶものではありません。WHERE はグループ化に参加する行を絞り込み、HAVING は出力するグループを絞り込みます。実務のクエリでは、両方を組み合わせて使うのが一般的です。

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada', 50, 'paid'),
    ('Ada', 30, 'refunded'),
    ('Boris', 80, 'paid'),
    ('Boris', 20, 'paid'),
    ('Cleo', 200, 'paid'),
    ('Cleo', 50, 'refunded');

SELECT customer, SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 75;

実行順に上から下へ読んでいきましょう。

1. WHERE status = 'paid' — 返金済みの行はここで丸ごと除外。
2. GROUP BY customer — 残った行を顧客ごとにバケット分け。
3. SUM(amount) がグループ単位で計算される。
4. HAVING SUM(amount) > 75 — 条件を満たすグループだけを残す。

生き残るのは Boris (80 + 20 = 100) と Cleo (200) の2人。Ada は支払い済みの注文が 50 だけだったので、しきい値に届きません。

複数条件と複数カラムでのグループ化

HAVING でも WHERE と同じブール演算子(AND、OR、NOT)が使えます。さらに、複数のカラムで GROUP BY すれば、サブバケット単位で集計できます。

CREATE TABLE sales (
    region TEXT,
    quarter TEXT,
    amount REAL
);

INSERT INTO sales VALUES
    ('North', 'Q1', 100), ('North', 'Q1', 50),
    ('North', 'Q2', 300),
    ('South', 'Q1', 80),
    ('South', 'Q2', 120), ('South', 'Q2', 60);

SELECT region, quarter, SUM(amount) AS total, COUNT(*) AS deals
FROM sales
GROUP BY region, quarter
HAVING SUM(amount) > 100 AND COUNT(*) >= 2;

(region, quarter) の組み合わせごとに、それぞれが別々のグループとして扱われます。HAVING 句では、合計が 100 を超えていること かつ 取引件数が 2 件以上あること、という 2 つの条件を満たす必要があります。条件を通過するのは ('North', 'Q1') と ('South', 'Q2') の 2 グループだけです。

実践テクニック:重複データを見つける

カラム内の重複値を探すときの定番が、GROUP BY ... HAVING COUNT(*) > 1 というパターンです。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO users (email) VALUES
    ('ada@example.com'),
    ('boris@example.com'),
    ('ada@example.com'),
    ('cleo@example.com'),
    ('boris@example.com');

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

重複が2件浮かび上がりました。ここから先はアカウントを統合するか、UNIQUE制約を追加するか、データをクレンジングするか――対応はケースバイケースですが、重複を見つけ出すためのクエリの形はいつも同じです。

GROUP BY なしで HAVING を使う

あまり見かけませんが、これも文法上は有効です。GROUP BYを書かない場合、結果セット全体がひとつのグループとして扱われ、HAVINGはそのグループまるごとに対してフィルタをかけます。条件を満たせば集計値がすべて返り、満たさなければ何も返りません。

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (50), (30), (80);

SELECT COUNT(*) AS total_orders, SUM(amount) AS revenue
FROM orders
HAVING SUM(amount) > 100;

結果が1行だけ返ってくるのは、合計が160だからです。しきい値を > 200 に変えると、今度は1行も返らなくなります。実務では HAVING と GROUP BY をセットで使うのがほぼ定番ですが、構文上は必須ではないことも知っておくと便利です。

まとめ

• GROUP BY は行をキーごとのグループにまとめ、集計関数は各グループの中で動く。
• SELECT に書く非集計カラムは、基本的にすべて GROUP BY にも入れる。
• WHERE はグループ化の前に行を絞り込み、HAVING はグループ化の後にグループを絞り込む。
• COUNT(*) や SUM(...) のような集計関数は HAVING で使うもので、WHERE には書けない。
• HAVING は複数条件をAND/ORで組み合わせられるし、SELECT で付けたエイリアスも参照できる。

次回:外部キー

1つのテーブルを集計するだけでも十分役立ちますが、実際のスキーマではデータが複数のテーブルにまたがるのが普通です。注文はこっち、顧客はあっち、商品はさらに別のテーブル、という具合ですね。こうしたテーブル同士をつなぎ、関係性を一貫した形で保つ仕組みが外部キー(Foreign Key)です。次の章で詳しく見ていきましょう。