SQLite集計関数まとめ：COUNT/SUM/AVG/MIN/MAX

集計関数とは何をするものか

ここまでに出てきた SQL 関数の多くは、行ごとに動くものでした。UPPER(name) なら 1 行ごとに 1 回、ROUND(price, 2) も 1 行ごとに 1 回実行されます。ところが SQLite の集計関数はちょっと違います。行の集合全体をまとめて見て、1 つの値にギュッと畳み込むのが仕事です。

まずは練習用に小さなテーブルを用意しましょう。

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

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada',   42.00, 'paid'),
    ('Boris', 15.50, 'paid'),
    ('Ada',   88.00, 'paid'),
    ('Carl',  10.00, 'refunded'),
    ('Ada',   NULL,  'pending');

SELECT COUNT(*), SUM(amount), AVG(amount) FROM orders;

行が5つ入って、戻ってくるのは1行だけ。SQLite の集計関数のイメージはまさにこれです。複数の行をひとつのサマリにギュッと潰してくれる、それが集計関数。GROUP BY を付けなければ、結果セット全体がまるごと1行に集約されます。

COUNT の使い方：行を数えるか、値を数えるか

COUNT には3つの書き方があり、この違いがけっこう効いてきます。

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

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Ada',   88.00),
    ('Carl',  NULL),
    ('Ada',   NULL);

SELECT
    COUNT(*)               AS rows_total,
    COUNT(amount)          AS rows_with_amount,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

• COUNT(*) は行数を数えます。NULL も含め、必ず数値を返します。
• COUNT(列名) はその列の NULL 以外の値だけを数えます。
• COUNT(DISTINCT 列名) は NULL を除いたユニークな値の個数を返します。

行数は 5 件、amount が入っているのは 3 件、顧客は 3 人。COUNT(amount) が COUNT(*) より小さくなって「あれ?」となったときは、これが原因です。NULL はカウント対象外、というわけですね。

SUM・AVG・MIN・MAX の使い方

数値系の集計関数は想像どおりの動きをしますが、ひとつだけ覚えておきたいルールがあります。いずれも NULL は無視するという点です。

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT
    SUM(amount) AS total,
    AVG(amount) AS mean,
    MIN(amount) AS smallest,
    MAX(amount) AS largest,
    COUNT(amount) AS counted
FROM orders;

AVG の結果は (10 + 20 + 30) / 3 = 20.0 であって、60 / 4 = 15.0 にはなりません。分母になるのは NULL を除いた件数です。もし「欠損値を 0 として扱いたい」のであれば、その意図をはっきり書いてあげる必要があります。

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT AVG(COALESCE(amount, 0)) FROM orders;

MIN と MAX はテキストや日付にも使えます。テキストの場合は辞書順で比較され、日付は標準のISO形式の文字列として比較されます。

SUM と TOTAL の違い

SQLite にはもう一つ、SUM に似た集計関数として TOTAL が用意されています。これは SUM の気になる挙動を 2 つ解消してくれるものです。

CREATE TABLE empty_table (amount REAL);

SELECT SUM(amount), TOTAL(amount) FROM empty_table;

• SUM は対象行が 0 件のとき NULL を返しますが、TOTAL は 0.0 を返します。
• 値がすべて NULL の場合も、SUM は NULL、TOTAL は 0.0 になります。
• TOTAL は常に浮動小数点数を返すので、整数演算でオーバーフローする心配がありません。

ただしトレードオフもあります。TOTAL は SQL 標準ではなく、戻り値が必ず REAL になるため、整数を期待しているとハマることがあります。「行がなければ 0 として扱いたい」アプリでは TOTAL が便利ですが、SQL 標準の挙動に揃えたいなら SUM を使いましょう。

集計関数の中で DISTINCT を使う

DISTINCT は COUNT 専用ではなく、どの集計関数の中でも使えます。集計が走る 前 に重複値を取り除いてくれるのがポイントです。

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Carl',  10.00);

SELECT
    SUM(amount)          AS sum_all,
    SUM(DISTINCT amount) AS sum_unique,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

SUM(amount) は全行の amount を足し合わせます。一方の SUM(DISTINCT amount) はユニークな値だけを 1 回ずつ加算するもので、「請求金額のユニーク合計」のような場面では使えますが、実際にはあまり登場しません。よく使うのは COUNT(DISTINCT customer) の方です。

FILTER句で一部の行だけ集計する

一部の行だけを集計したいときに最初に思いつくのは WHERE でしょう。ただし WHERE はクエリ全体に効いてしまうため、同じクエリの中で「支払い済み注文の件数」と「返金の件数」を同時に取る、といった使い方ができません。これを解決してくれるのが sqlite の FILTER 句です:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'),
    (15.50, 'paid'),
    (88.00, 'paid'),
    (10.00, 'refunded'),
    (25.00, 'pending');

SELECT
    COUNT(*) FILTER (WHERE status = 'paid')      AS paid_count,
    COUNT(*) FILTER (WHERE status = 'refunded')  AS refund_count,
    SUM(amount) FILTER (WHERE status = 'paid')   AS paid_revenue
FROM orders;

FILTER (WHERE ...) 句は、その集計関数ひとつだけに条件を効かせる仕組みです。テーブルを一度なめるだけで、切り口の違う集計を同時に取れるのが強み。FILTER が使えなかった時代は SUM(CASE WHEN status = 'paid' THEN amount END) と書いていましたが、やっていることは同じで、ただ記述量が多かっただけです。

GROUP_CONCAT で文字列を連結する

GROUP_CONCAT は他の集計関数と毛色が違います。数値ではなく、値をつなげて1つの文字列として返してくれます。

CREATE TABLE tags (post_id INTEGER, tag TEXT);
INSERT INTO tags VALUES
    (1, 'sqlite'),
    (1, 'database'),
    (1, 'tutorial'),
    (2, 'python'),
    (2, 'sqlite');

SELECT
    post_id,
    GROUP_CONCAT(tag)        AS default_separator,
    GROUP_CONCAT(tag, ' | ') AS pipe_separated
FROM tags
GROUP BY post_id;

デフォルトの区切り文字はカンマです。別の文字を使いたいときは第2引数で指定します。並び順は保証されないので、GROUP_CONCAT(tag ORDER BY tag) のようにラップしておくと安心です。UI に表示するときに順番が安定するので便利です。

GROUP BY なしで集計する

ここまでの例で GROUP BY を使わずに集計関数を書いたケースは、すべて結果が1行だけでした。これがルールです。GROUP BY のない SELECT で集計関数を使うと、WHERE で絞り込んだあとのテーブル全体を1行にまとめたサマリーになります。

集計関数は自由に組み合わせて使えます。

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'), (15.50, 'paid'), (88.00, 'paid'),
    (10.00, 'refunded'), (25.00, 'pending');

SELECT
    COUNT(*)    AS orders,
    SUM(amount) AS revenue,
    AVG(amount) AS average,
    MIN(amount) AS smallest,
    MAX(amount) AS largest
FROM orders
WHERE status = 'paid';

ただし、集約していない列と集計関数を混在させて、まともな結果を期待するのは無理です。

-- SQLite では許可されているが、`customer` の値は任意のものになる。
SELECT customer, SUM(amount) FROM orders;

SQLite ではエラーになりませんが（他の DB ではエラーです）、合計と一緒に表示されるのは適当に選ばれた顧客の名前になってしまいます。顧客ごとの合計が欲しいなら GROUP BY の出番です。次のページで扱います。

次のステップ：GROUP BY と HAVING

テーブル全体に集計関数をかけると「全体でいくら？」がわかります。でも、顧客ごと・月ごと・ステータスごとといったグループ単位で集計してこそ、本当に知りたいことが見えてきます。GROUP BY は集計の前に行をグループ（バケット）に分けるための構文で、HAVING は集計後の結果に対してフィルタをかけるための構文です。次のページで詳しく見ていきましょう。