集計関数 — COUNT / SUM / AVG / MIN / MAX

本記事で使うデータ — score テーブル

本記事の 集計関数（集約関数とも呼ぶ。複数行をまとめて 1 つの値にする関数）では、何行あるか、合計はいくつか、平均は、最小・最大は——というように、多数の行を 1 つの数値に圧縮 します。COUNT / SUM / AVG / MIN / MAX の 5 つが基本で、レポートの集計値はほぼこれだけで作れます。

題材は score テーブル（30 行 = 10 名 × Math / English / Science の 3 科目）です。score 列の点数を対象に、全体の集計と、WHERE で対象を絞ってからの集計を順に試します。グループごとの集計（科目別平均など）は次の記事で扱うので、本記事は テーブル全体／WHERE で絞った範囲全体を 1 つの値にまとめる ところに集中します。

演習に入る前に、score テーブルの 列定義 と データのサンプル を確認しておきます。

① PRAGMA table_info(score); で列名・型・主キーを確認してください。

② SELECT * FROM score LIMIT 5; で先頭 5 行のデータをプレビューしてください。

集計関数は「多数の行 → 1 つの値」

集計関数は SELECT の列の位置に書きます。

ここで気をつけたいのが、素の列と集計関数を同じ `SELECT` に混ぜない ことです。name のような素の列は 行ごとに値が違います が、COUNT(*) などの集計関数は テーブル全体（または `WHERE` で絞った全体）を 1 つの値に畳みます。SELECT name, COUNT(*) FROM score; のように両方を書くと、集計側は全体で 1 値なのに name は「どの行の値を出すか」が決まりません。多くのデータベースではこれは エラー になります。

-- 件数・種類数・合計・平均をまとめて取得
SELECT
  COUNT(*)             AS row_count,
  COUNT(DISTINCT name) AS name_kinds,
  SUM(score)           AS total,
  AVG(score)           AS avg_raw
FROM score
WHERE subject = 'English';

COUNT(*) と COUNT(列) の違い

COUNT(*) は 行数そのもの を数えます。COUNT(列) は その列が NULL でない行だけ を数えます。score テーブルには NULL がないため両者は同じ 30 になりますが、NULL を含む列ではここに差が出ます（後半で NULL のある表を使って確かめます）。さらに COUNT(DISTINCT 列) と書くと 重複を除いた種類数 を数えられます（subject に対して使えば科目の種類数 3 が得られます）。

Tips — 集計関数と NULL

SUM / AVG / MIN / MAX は NULL の行を計算に入れません（無視します）。とくに AVG は「NULL でない値の合計 ÷ NULL でない件数」で計算されます。そのため NULL を含む列では、SUM(列) / COUNT(*)（分母が全行数）と AVG(列)（分母が NULL でない件数）は 値がずれます。NULL を 0 とみなして平均したいときは AVG(COALESCE(列, 0)) のように明示します。

「成績データ全体の規模感をダッシュボードに 1 行で出したい」という要件を想定します。（正しく実行できれば解説が表示されます）

① score テーブルから、5 つの集計関数を使って次の 5 列を 1 行で取り出してください。

② 全行の件数を row_count、点数の合計を total、点数の平均を avg_score、最低点を min_score、最高点を max_score という別名で、この順に並べてください。

③ 平均は小数が長く表示されるため、小数第 2 位までに丸めて avg_score としてください。

WHERE で対象を絞ってから集計する

WHERE を付けると、条件に合う行だけ が集計関数の入力になります。実行順序は「FROM で全行を読む → WHERE で行を絞る → 残った行を集計関数でまとめる」です。つまり集計関数が見るのは WHERE を通過した行だけで、絞り込みの後に合計や平均が計算されます。WHERE subject = 'Math' を付ければ Math の 10 行だけが対象になり、平均は Math だけの平均になります。

-- Science の 10 行だけの 件数 / 平均 / 最高点
SELECT
  COUNT(*)             AS row_count,
  ROUND(AVG(score), 1) AS avg_science,
  MAX(score)           AS top_science
FROM score
WHERE subject = 'Science';

「Math（数学）の成績だけを切り出して、平均・最高・最低を出したい」という要件を想定します。

① score テーブルから、subject が Math の行だけを対象にしてください。

② 対象行の件数を math_count、平均点を avg_math、最高点を max_math、最低点を min_math という別名で、この順に 1 行で取り出してください。

③ 平均は小数第 2 位までに丸めてください。

COUNT(列) と NULL、AVG の丸め

ここまでの score には NULL がありませんでした。NULL の有無で集計がどう変わるかを、欠損のある customer テーブル（8 名・age / email / country に NULL あり）で確かめます。COUNT(*) は 行数 なので 8 のままですが、COUNT(email) は email が入っている行だけを数えるため 4 になります。NULL を含む列を COUNT(列) すると、このように COUNT(*) より小さくなります。

また AVG は割り算なので 31.5 のように小数が出ます。レポートでは ROUND(AVG(age), 2) のように ROUND(式, 桁数) で丸めると扱いやすくなります。

-- NULL の有無で COUNT と AVG がどう変わるか
SELECT
  COUNT(*)            AS rows_all,
  COUNT(country)      AS with_country,
  ROUND(AVG(age), 2)  AS avg_age
FROM customer;

「会員データには未入力（NULL）の項目がある。全件数と、実際に値が入っている件数を比べたい」という要件を想定します。

① customer テーブルから、全行数を all_rows、email が入っている件数を with_email、age が入っている件数を with_age、country の種類数を country_kinds という別名で、この順に 1 行で取り出してください。

② NULL のある列を COUNT(列) すると COUNT(*) より小さくなることを確認してください。

「全体平均を上回った成績だけを取り出して、その件数と平均を見たい」という要件を想定します。全体平均は 77.93… です（前の演習で確認しました）。

① score テーブルから、score が 78 以上の行だけを対象にしてください。

② 対象行の件数を high_count、その平均点を avg_high、最高点を max_high という別名で、この順に 1 行で取り出してください。

③ 平均は小数第 2 位までに丸めてください。