「部門ごとの給与合計は出せた。でも、その部門合計の平均値はどうやって出すの？」
Oracle Database で集計処理を行う際、一度 GROUP BY でまとめた結果に対して、さらに AVG や MAX を使いたい場面があります。
本記事では、scott スキーマの EMP テーブルを使用し、集計結果をさらに集計するテクニック（サブクエリおよび集計関数のネスト）と、小計・総計を自動算出する ROLLUP について解説します。
SCOTTサンプルスキーマは以下を実行することでインポートおよび使用が可能です。
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
結論・やり方リスト
集計結果をさらに集計するには、主に2つの方法があります。
- サブクエリを使う:
FROM句で集計結果を一時テーブルのように扱う（標準的）。 - 集計関数をネストする:
AVG(SUM(SAL))のように関数を重ねる（Oracle独自機能）。
また、合計行を一度に出すには ROLLUP が最適です。
【この記事でわかること】
- 部門別合計の「平均」や「最大」を求めるSQL
ROLLUPを使って「総計」行を1回のクエリで出す方法NVLを使った NULL 対策
1. 前提データ：EMPテーブルの確認
今回は Oracle 標準の scott.emp テーブルを使用します。まずはデータの中身と、これから集計する対象を確認しましょう。
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP ORDER BY DEPTNO;
SQL> SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP ORDER BY DEPTNO;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7782 CLARK MANAGER 2450 10
7839 KING PRESIDENT 5000 10
7934 MILLER CLERK 1300 10
7566 JONES MANAGER 2975 20
7902 FORD ANALYST 3000 20
7876 ADAMS CLERK 1100 20
7369 SMITH CLERK 800 20
7788 SCOTT ANALYST 3000 20
7521 WARD SALESMAN 1250 30
7844 TURNER SALESMAN 1500 30
7499 ALLEN SALESMAN 1600 30
7900 JAMES CLERK 950 30
7698 BLAKE MANAGER 2850 30
7654 MARTIN SALESMAN 1250 30
14 rows selected.
データのポイント:
DEPTNO（部門番号）: 10, 20, 30 の3部門が存在。SAL（給与）: 各社員の給与。- 目標: まず部門ごとに
SALを合計し、その数値をさらに分析します。
2. Step 1: 基本のGROUP BY（部門別集計）
まずは基礎となる「部門ごとの給与合計」を算出します。
SELECT
DEPTNO,
SUM(SAL) AS DEPT_TOTAL
FROM EMP
GROUP BY DEPTNO;
実行結果:
SQL> SELECT
2 DEPTNO,
3 SUM(SAL) AS DEPT_TOTAL
4 FROM EMP
5 GROUP BY DEPTNO;
DEPTNO DEPT_TOTAL
---------- ----------
30 9400
10 8750
20 10875
この「9400, 10875, 8750」という3つの数字が、次のステップの入力データになります。
3. Step 2: 集計結果をさらに集計する
ここからが本題です。「部門ごとの給与合計（3つの数字）」の平均値を計算します。
(9400 + 10875 + 8750) ÷ 3 = 9675 となれば正解です。
方法A：サブクエリ（インラインビュー）を使用する
最も汎用的で、他のデータベース（MySQLやPostgreSQLなど）でも通用する方法です。Step 1 のSQLを FROM 句の中に入れます。
SELECT
AVG(DEPT_TOTAL) AS AVG_OF_TOTALS
FROM
(SELECT DEPTNO, SUM(SAL) AS DEPT_TOTAL
FROM EMP
GROUP BY DEPTNO);
解説:
内側の SELECT で3行の集計結果を作り、外側の SELECT でその3行を平均しています。
方法B：集計関数のネスト（Oracleの便利機能）
Oracle Database では、集計関数を入れ子（ネスト）にすることができます。非常に短いコードで記述可能です。
SELECT
AVG(SUM(SAL)) AS AVG_OF_TOTALS
FROM EMP
GROUP BY DEPTNO;
実行結果（A/B共通）:
SQL> SELECT
2 AVG(DEPT_TOTAL) AS AVG_OF_TOTALS
3 FROM
4 (SELECT DEPTNO, SUM(SAL) AS DEPT_TOTAL
5 FROM EMP
6 GROUP BY DEPTNO);
AVG_OF_TOTALS
-------------
9675
SQL> SELECT
2 AVG(SUM(SAL)) AS AVG_OF_TOTALS
3 FROM EMP
4 GROUP BY DEPTNO;
AVG_OF_TOTALS
-------------
9675
注意点:
集計関数をネストした場合（例: AVG(SUM(SAL))）、SELECT 句に DEPTNO などのグループ化列を表示することはできません（個別の部門番号を表示しつつ、全体の平均を出すことは論理的に矛盾するため）。
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
4. Step 3: ROLLUPで「総計」を自動追加する
「部門別の合計も出したいし、最後に全社の総計も載せたい」。
これを実現するために UNION ALL でSQLを2本繋ぐ必要はありません。ROLLUP オプションを使えば一発です。
SELECT
DEPTNO,
SUM(SAL) AS TOTAL_SAL
FROM EMP
GROUP BY ROLLUP(DEPTNO);
実行結果:
SQL> SELECT
2 DEPTNO,
3 SUM(SAL) AS TOTAL_SAL
4 FROM EMP
5 GROUP BY ROLLUP(DEPTNO);
DEPTNO TOTAL_SAL
---------- ----------
10 8750
20 10875
30 9400
29025
解説:
GROUP BY ROLLUP(DEPTNO) と記述するだけで、Oracle が自動的に「グループ化なしの集計行（総計）」を最後に追加してくれます。DEPTNO が NULL になっている行が総計です。
5. 応用：見た目を整える（NULL対策）
ROLLUP の結果で DEPTNO が (null) になるのが気になる場合は、NVL 関数や DECODE 関数を使って「Total」などの文字列に置き換えると、レポートとしての品質が上がります。
SELECT
DECODE(GROUPING(DEPTNO), 1, 'Total', DEPTNO) AS DEPT_LABEL,
SUM(SAL) AS TOTAL_SAL
FROM EMP
GROUP BY ROLLUP(DEPTNO);
SQL> SELECT
2 DECODE(GROUPING(DEPTNO), 1, 'Total', DEPTNO) AS DEPT_LABEL,
3 SUM(SAL) AS TOTAL_SAL
4 FROM EMP
5 GROUP BY ROLLUP(DEPTNO);
DEPT_LABEL TOTAL_SAL
---------------------------------------- ----------
10 8750
20 10875
30 9400
Total 29025
解説:
GROUPING(DEPTNO): 集計行（ROLLUPでできた行）なら1、通常のデータなら0を返す関数です。- これを利用して「集計行なら ‘Total’ と表示」させています。
- ※
DEPTNOは数値型のため、文字列 ‘Total’ を混ぜるにはTO_CHAR変換などが必要になる場合がありますが、ここでは概念として紹介します。
- ※
6. トラブルシューティング・注意点
| 項目 | 注意点 | 対処法 |
| NULLデータの集計 | SUM や AVG は NULL を無視します。 | NULL を 0 として扱いたい場合は SUM(NVL(COMM, 0)) のように変換してください。 |
| ネスト時の列表示 | SELECT DEPTNO, AVG(SUM(SAL))... はエラーになります（ORA-00937）。 | ネスト集計時は、グループ化キー（DEPTNO）を SELECT 句から外してください。 |
| データ型 | 数値以外の列（名前など）を SUM しないように。 | 集計対象のカラム定義を確認しましょう。 |
7. FAQ：よくある質問
Q1. GROUP BY ROLLUP と CUBE の違いは？
A. ROLLUP は階層的な集計（例: 年 > 月 > 日 > 総計）を行いますが、CUBE は指定した列のあらゆる組み合わせ（クロス集計）を出力します。単純な総計が欲しいだけなら ROLLUP で十分です。
Q2. WHERE句で絞り込んだ場合、ROLLUPの総計はどうなりますか？
A. WHERE 句で絞り込まれた後のデータのみで総計が計算されます。除外されたデータは総計にも含まれません。
Q3. 集計関数のネストは3階層以上できますか？
A. いいえ、Oracle でも集計関数のネストは 2階層まで（例: MAX(AVG(SUM(SAL))) は不可）です。それ以上必要な場合はサブクエリを使用してください。
まとめ
- 再集計:
GROUP BYの結果をさらに集計したい場合は、サブクエリ または 集計関数のネストAVG(SUM(col))を使う。 - 総計:
UNIONを使わずROLLUPを使うのがスマート。 - NULL: 集計行の NULL 表示は
GROUPING関数などで制御可能。
集計レポート作成の工数を大幅に減らすテクニックです。ぜひ実務で活用してみてください。
本記事は Oracle Database 19c を対象に解説します（他バージョンは画面や既定値が異なる場合があります）。
[参考]
Oracle Database SQL言語リファレンス 19c
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
コメント