【DB入門】第9章 集計する

「社員は何人いるか」「平均年齢は何歳か」「部署ごとに何人いるか」のように、複数の行をまとめて1つの値を求めることを 集計 といいます。この章では、集計関数（COUNT・SUM・AVG・MAX・MIN）と、行をグループに分ける GROUP BY、集計した結果を絞り込む HAVING を学びます。

この章で学ぶこと

• 集計関数：COUNT・SUM・AVG・MAX・MIN
• GROUP BY でグループごとに集計する
• HAVING で集計結果を絞り込む。WHERE との違い
• SELECT 文の句の順番と、処理される順

9.1 行を数える：COUNT

COUNT(*) は、行の数を返します。社員が何人いるかを数えます。

ワークシート（XE_DBAPP）

SELECT COUNT(*) FROM EMPLOYEE;

COUNT(*)
10

結果は 1行 です。10行のデータをまとめて1つの値にしているので、結果の行は1つになります。これが集計の特徴です。

WHERE で絞り込んでから数えることもできます。営業部（部署ID 2）の社員数を数えます。

ワークシート（XE_DBAPP）

SELECT COUNT(*) AS CNT FROM EMPLOYEE WHERE DEPT_ID = 2;

CNT
4

集計した列の見出しは COUNT(*) のような式になるので、AS で別名を付けると読みやすくなります。

【知識メモ】COUNT(列名) と COUNT(DISTINCT 列名)

COUNT(列名) と書くと、その列が NULL でない 行だけを数えます。この教材のテーブルには NULL が無いので COUNT(*) と同じ結果ですが、第10章の結合では違いが出ます。COUNT(DISTINCT ADDRESS) と書くと、ADDRESS の値の種類の数（この教材では 6）を数えます。

9.2 合計・平均・最大・最小

関数	意味	使える列
COUNT(*)	行の数	—
SUM(列名)	合計	数
AVG(列名)	平均	数
MAX(列名)	最大値	数・文字・日付
MIN(列名)	最小値	数・文字・日付

社員の年齢の最大・最小・平均・合計を求めます。

ワークシート（XE_DBAPP）

SELECT MAX(AGE), MIN(AGE), AVG(AGE), SUM(AGE) FROM EMPLOYEE;

MAX(AGE)	MIN(AGE)	AVG(AGE)	SUM(AGE)
42	22	28.6	286

複数の集計関数を1つの SELECT に並べて書けます。平均は小数になることがあるので、第8章の ROUND で桁をそろえるのが一般的です。

ワークシート（XE_DBAPP）

SELECT MAX(AGE) AS MAX_AGE, MIN(AGE) AS MIN_AGE, ROUND(AVG(AGE), 1) AS AVG_AGE, SUM(AGE) AS SUM_AGE FROM EMPLOYEE;

MAX_AGE	MIN_AGE	AVG_AGE	SUM_AGE
42	22	28.6	286

MAX・MIN は日付にも使えます。最も早い生年月日と最も遅い生年月日を求めます。

ワークシート（XE_DBAPP）

SELECT MIN(BIRTH_DATE), MAX(BIRTH_DATE) FROM EMPLOYEE;

MIN(BIRTH_DATE)	MAX(BIRTH_DATE)
1984-02-10 00:00:00	2004-05-20 00:00:00

【ご注意】集計関数と普通の列は一緒に書けない

SELECT NAME, MAX(AGE) FROM EMPLOYEE; のように、「最年長の人の名前」を求めるつもりで書くと、「ORA-00937: 単一グループのグループ関数ではありません。」というエラーになります。MAX(AGE) は10行をまとめた1つの値ですが、NAME は10行それぞれの値なので、1行の結果に並べられないためです。集計関数と一緒に書ける列は、次の GROUP BY に書いた列だけです。最年長の人の名前を知りたいときは、SELECT NAME, AGE FROM EMPLOYEE ORDER BY AGE DESC; の先頭の行を見ます。

9.3 グループごとに集計する：GROUP BY

「部署ごとの社員数」のように、ある列の値が同じ行をグループにまとめて、グループごとに集計するのが GROUP BY です。

ワークシート（XE_DBAPP）

SELECT DEPT_ID, COUNT(*) AS CNT FROM EMPLOYEE GROUP BY DEPT_ID ORDER BY DEPT_ID;

DEPT_ID	CNT
1	2
2	4
3	4

10行が、DEPT_ID の値（1・2・3）で3つのグループに分かれ、グループごとに COUNT(*) が計算されました。結果は グループの数だけ（3行）になります。社員のいない部署ID 4 は、社員テーブルに行が無いので表示されません（部署テーブルと結合すれば表示できます。第10章）。

図9-1 GROUP BY DEPT_ID で3つのグループに分けて集計する

グループごとに、複数の集計を並べて求められます。

ワークシート（XE_DBAPP）

SELECT DEPT_ID, COUNT(*) AS CNT, ROUND(AVG(AGE), 1) AS AVG_AGE, MAX(AGE) AS MAX_AGE, MIN(AGE) AS MIN_AGE
  FROM EMPLOYEE
 GROUP BY DEPT_ID
 ORDER BY DEPT_ID;

DEPT_ID	CNT	AVG_AGE	MAX_AGE	MIN_AGE
1	2	28	29	27
2	4	30.3	42	22
3	4	27.3	35	24

性別ごとの人数と平均年齢を求めます。

ワークシート（XE_DBAPP）

SELECT GENDER, COUNT(*) AS CNT, ROUND(AVG(AGE), 1) AS AVG_AGE FROM EMPLOYEE GROUP BY GENDER ORDER BY GENDER;

GENDER	CNT	AVG_AGE
1	5	31.6
2	5	25.6

都道府県ごとの人数を、人数の多い順に並べます。ORDER BY には集計した列の別名を書けます（WHERE と違い、ORDER BY は SELECT の後に処理されるためです）。

ワークシート（XE_DBAPP）

SELECT ADDRESS, COUNT(*) AS CNT FROM EMPLOYEE GROUP BY ADDRESS ORDER BY CNT DESC, ADDRESS;

ADDRESS	CNT
東京都	3
京都府	2
大阪府	2
兵庫県	1
愛知県	1
神奈川県	1

複数の列でグループに分ける

GROUP BY に列を , で区切って複数書くと、その組み合わせごとにグループになります。部署と性別の組み合わせごとの人数を求めます。

ワークシート（XE_DBAPP）

SELECT DEPT_ID, GENDER, COUNT(*) AS CNT FROM EMPLOYEE GROUP BY DEPT_ID, GENDER ORDER BY DEPT_ID, GENDER;

DEPT_ID	GENDER	CNT
1	2	2
2	1	3
2	2	1
3	1	2
3	2	2

総務部（1）には男性がいないので、「1, 1」の組み合わせの行はありません。人数 0 の行は作られないことに注意してください。

GROUP BY の決まり

【再確認】SELECT に書けるのは、GROUP BY の列と集計関数だけ

GROUP BY を使う SELECT では、SELECT に書けるのは GROUP BY に書いた列 と 集計関数 だけです。SELECT NAME, COUNT(*) FROM EMPLOYEE GROUP BY DEPT_ID; のように、GROUP BY に無い列（NAME）を書くと、「ORA-00979: GROUP BYの式ではありません。」というエラーになります。グループの中には複数の人がいるので、「そのグループの NAME」が1つに決まらないためです。

9.4 集計結果を絞り込む：HAVING

「社員が4人以上いる部署」のように、集計した結果 で絞り込みたいときは、GROUP BY の後ろに HAVING 条件 を書きます。

ワークシート（XE_DBAPP）

SELECT DEPT_ID, COUNT(*) AS CNT FROM EMPLOYEE GROUP BY DEPT_ID HAVING COUNT(*) >= 4 ORDER BY DEPT_ID;

DEPT_ID	CNT
2	4
3	4

社員が2人の総務部（1）が除かれました。HAVING は、グループに分けて集計した後に、グループごと 残すか除くかを決めます（図9-2）。

図9-2 HAVING COUNT() >= 4 で、条件を満たすグループだけを残す*

平均年齢が28歳以上の部署を求めることもできます。

ワークシート（XE_DBAPP）

SELECT DEPT_ID, ROUND(AVG(AGE), 1) AS AVG_AGE FROM EMPLOYEE GROUP BY DEPT_ID HAVING AVG(AGE) >= 28 ORDER BY DEPT_ID;

DEPT_ID	AVG_AGE
1	28
2	30.3

WHERE と HAVING の違い

どちらも「絞り込む」ための句ですが、絞り込む対象が違います。

句	絞り込む対象	いつ処理されるか	集計関数
WHERE	テーブルの 1行1行	グループに分ける 前	書けない
HAVING	集計した後の グループ	グループに分けて集計した 後	書ける

WHERE COUNT(*) >= 4 と書くと、「ORA-00934: ここではグループ関数は使用できません。」というエラーになります。WHERE が処理される時点では、まだグループに分けられておらず、数えることができないためです。集計関数を使う条件は、必ず HAVING に書きます。

HAVING にも、WHERE と同じく、SELECT で付けた別名は書けません。HAVING CNT >= 4 は「ORA-00904: "CNT": 無効な識別子です。」になるので、HAVING COUNT(*) >= 4 と集計関数をもう一度書きます。

【ご注意】mac（Oracle AI Database 26ai）では別名を書いてもエラーにならない

mac で使っている Oracle AI Database 26ai では、HAVING や GROUP BY に、SELECT で付けた別名を書けるようになりました。そのため、HAVING CNT >= 4 を実行しても ORA-00904 にならず、結果が表示されます。

ただし、Oracle Database 21c など、現場で多く使われている版ではエラーになります。どの版の Oracle でも動くように、教材どおり、HAVING には COUNT(*) のような集計関数をそのまま書いてください。

9.5 WHERE・GROUP BY・HAVING・ORDER BY を組み合わせる

これまでに学んだ句をすべて使った SELECT 文を書いてみましょう。「25歳以上の社員だけを対象に、部署ごとの人数を数え、2人以上いる部署を、部署IDの順に表示する」という SQL です。

ワークシート（XE_DBAPP）

SELECT DEPT_ID, COUNT(*) AS CNT
  FROM EMPLOYEE
 WHERE AGE >= 25
 GROUP BY DEPT_ID
HAVING COUNT(*) >= 2
 ORDER BY DEPT_ID;

DEPT_ID	CNT
1	2
2	3
3	2

処理の順に追ってみます。

1. FROM EMPLOYEE：社員テーブルの10行から始める
2. WHERE AGE >= 25：25歳以上の7人に絞る（林檎 24・田中 24・伊藤 22 が除かれる）
3. GROUP BY DEPT_ID：部署ごとに分ける（1：2人、2：3人、3：2人）
4. HAVING COUNT(*) >= 2：2人以上の部署に絞る（すべて残る）
5. SELECT DEPT_ID, COUNT(*)：表示する列を決める
6. ORDER BY DEPT_ID：並べ替える

SELECT 文の句は、必ずこの順番で書きます。

書き方

SELECT 列名, 集計関数
  FROM テーブル名
 WHERE 行の条件
 GROUP BY 列名
HAVING グループの条件
 ORDER BY 列名;

図9-3 SELECT 文が処理される順（第6章の図に GROUP BY と HAVING を加えたもの）

9.6 うまくいかないときは

症状	考えられる原因と対処
「ORA-00937: 単一グループのグループ関数ではありません。」	集計関数と普通の列を一緒に書いていて、GROUP BY がありません。普通の列を GROUP BY に書くか、SELECT から外してください。
「ORA-00979: GROUP BYの式ではありません。」	GROUP BY に書いていない列を、SELECT か HAVING に書いています。
「ORA-00934: ここではグループ関数は使用できません。」	WHERE に集計関数を書いています。HAVING に書いてください。
「ORA-00904: "CNT": 無効な識別子です。」	HAVING（または WHERE）に別名を書いています。集計関数をもう一度書いてください。
「ORA-00933: SQLコマンドが正しく終了されていません。」	句の順番が違います。WHERE → GROUP BY → HAVING → ORDER BY の順に書いてください。
人数 0 の部署が出てこない	社員テーブルだけを集計しているためです。部署テーブルと結合すると、社員のいない部署も表示できます（第10章）。

ほかのエラーは、付録B「よくあるエラーと対処法」 も参考にしてください。

9.7 まとめと確認問題

まとめ

• 集計関数は COUNT(*)（行数）・SUM（合計）・AVG（平均）・MAX（最大）・MIN（最小）。複数の行をまとめて1つの値にする
• GROUP BY 列名 で、その列の値が同じ行をグループにして、グループごとに集計する。結果はグループの数だけになる
• GROUP BY を使うとき、SELECT に書けるのは GROUP BY の列と集計関数だけ
• HAVING は集計した後のグループを絞り込む。WHERE はグループに分ける前の行を絞り込む。集計関数の条件は HAVING に書く
• 句の順番は SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY

この章で使った SQL は、付録C「SQL 早見表」 にまとめています。

確認問題

問1　女性（性別 2）の社員の人数を求める SQL を書いてください。

解答を見る

SELECT COUNT(*) FROM EMPLOYEE WHERE GENDER = 2;
結果は 5 です。

問2　都道府県ごとの社員の平均年齢を、小数第1位までで求める SQL を書いてください。

解答を見る

SELECT ADDRESS, ROUND(AVG(AGE), 1) AS AVG_AGE FROM EMPLOYEE GROUP BY ADDRESS ORDER BY ADDRESS;

問3　SELECT DEPT_ID, NAME, COUNT(*) FROM EMPLOYEE GROUP BY DEPT_ID; がエラーになる理由を答えてください。

解答を見る

NAME が GROUP BY に無い列だからです。1つの部署には複数の社員がいるので、グループの NAME が1つに決まりません。「ORA-00979: GROUP BYの式ではありません。」になります。

問4　「社員が2人以下の部署」を求めるには、WHERE COUNT(*) <= 2 と HAVING COUNT(*) <= 2 のどちらを使いますか。

解答を見る

HAVING COUNT(*) <= 2 です。集計関数を使う条件は、グループに分けて集計した後に処理される HAVING に書きます。WHERE に書くとエラー（ORA-00934）になります。

問5　SELECT DEPT_ID, COUNT(*) FROM EMPLOYEE WHERE GENDER = 1 GROUP BY DEPT_ID; の結果は何行で、それぞれの値は何ですか。

解答を見る

男性（性別 1）は、営業部（2）に 雷禅・佐藤・渡辺 の3人、開発部（3）に 林檎・高橋 の2人で、総務部にはいません。結果は2行で、「2, 3」と「3, 2」です。

---

前へ

第8章 関数を使う

次へ

第10章 テーブルを結合する