GROUP BY句によるグループグループ化
SELECT文にGROUP BY句を指定すると、行をグループ化することができます。なお、GROUP BY句は、必ずWHERE句の後ろ、かつORDER BY句の前に指定します(WHERE句やORDER BY句が指定される場合)。
▶ GROUP BY句を使用したSELECT文
SELECT 列名, グループ関数(列名)
FROM 表名
[WHERE 条件] [GROUP BY グループ化で使用する列のリスト] [ORDER BY 並び替えで使用する列のリスト]
GROUP BY句の基本構文は以下のとおりです。指定された列の値をもとにグループ化を行います(列の値が同じ行が、同一グループになります)、なお、GROUP BY句に複数の列を指定した場合は「複数の列の組み合わせが同じ行」が同一グループになります。
▶ GROUP BY句の基本構文
GROUP BY 列名 [, 列名 …]
なお、GROUP BY句を指定する際には以下の要件を満たす必要があります。
・GROUP BY句には1つ以上の列を指定する必要がある
・列別名は指定できない
・SELECT句の選択リストには「GROUP BY句で指定した列」と「グループ関数」のみ指定できる
・ORDER BY句とGROUP BY句を併用する場合、ORDER BY句には「GROUP BY句で指定した列」と「グループ関数」のみ指定できる
以下の例では、DEPTNO列(部門番号)の値をもとにグループ化を行い、各グループの「データ件数(社員数)」と「SAL列の平均値(平均給与)」を表示しています。
▶ GROUP BY句の基本的な使用例
SQL> SELECT deptno, COUNT(*), AVG(sal) 2 FROM employees 3 GROUP BY deptno; → DEPTNO列の値が同じ行をグループ化している DEPTNO COUNT(*) AVG(SAL)
----------- ------------ -------------
30 6 280833.333 20 5 234000
10 3 325000
もう1つ例を見てみましょう。以下の例では、DEPTNO列(部門番号)とJOB列(職種)の値が量とも等しいデータごとにグループ化を行い、各グループのデータ件数とSAL列(給与)の平均値を表示しています。ここでは「DEPTNO列の値が同じでも、JOB列の値が異なれば別グループになる」という点に注目してください。
▶ GROUP BY句によるデータのグループ化
SQL> SELECT deptno, job, COUNT(*), AVG(sal) 2 FROM employees 3 GROUP BY deptno, job; DEPTNO JOB COUNT(*) AVG(SAL)
----------- ----------- -----------
10 社長 1 500000 10 事務 1 230000
20 事務 2 155000 30 部長 1 285000 10 部長 1 245000 20 主任 2 290000
6行が選択されました。
※GROUP BY句を指定しても、GROUP BY句でソート順を指定しない限り、実行結果がどのような順番で表示されるかは保証されません。
■ グループ関数のネストとGROUP BY句
SELECT文にGROUP BY句の指定がある場合は、グループ関数は2レベルまでネストすることができます(グループ関数の引数に別のグループ関数を指定することができます)。
以下の例では、MAX関数の引数にAVG関数を指定する(グループ関数をネストする)ことで、SAL列の値(給与)の平均値が最も大きいグループの平均値を表示しています。
▶ グループ関数のネスト(1)
SQL> SELECT MAX(AVG(sal))
2 FROM employees
3 GROUP BY deptno;
MAX (AVG(SAL))
-----------------
325000
なお、グループ関数は3レベル以上ネストするとエラーになります。ネストできるレベルの上限が単一行関数とは異なるので注意してください(単一行関数は任意のレベルにネストできます)。
▶ グループ関数のネスト(2)
SQL> SELECT MAX(MAX(AVG(sal)))
2 FROM employees
3 GROUP BY deptno;
SELECT MAX(MAX(AVG(sal)))
*
行1でエラーが発生しました。:
ORA-00935: グループ関数のネスト・レベルが深すぎます。
■ GROUP BY句を指定したSELECT文のエラー
先述したとおり、GROUP BY句を指定する際にはいくつかの要件を満たす必要があります。当然、要件に半数る場合はエラーになります。
〇 列別名は指定できない
GROUP BY句には、列別名は指定できません。次の例では、GROUP BY句に列別名を指定しているためエラーになっています。
▶ エラーになるGROUP BY句(1)
SQL> SELECT deptno 部門番号, MAX(sal) 最高給与
2 FROM employees
3 GROUP BY 部門番号
4 ORDER BY 部門番号;
GROUP BY 部門番号
*
行3でエラーが発生しました。:
ORA-00904: "部門番号": 無効な識別子です。
※ORDER BY句には、列別名を指定できます。そのため、上記のエラーはGROUP BY句に指定されている列別名を、列名に変更することで解消できます。
〇 SELECT句の選択リストにはGROUP BY句で指定した列とグループ関数のみ指定できる
SELECT文にGROUP BY句を指定した場合、SELECT句の選択リストに指定できるのはGROUP BY句で指定した列とグループ関数のみです。GROUP BY句に指定されていない列を指定するとエラーになります。
以下の例では、GROUP BY句に指定されていないJOB列をSELECT句に指定しているためエラーになっています。
▶ エラーになるGROUP BY句(2)
SQL> SELECT deptno, job, COUNT(*), AVG(sal)
2 FROM employees
3 GROUP BY deptno;
SELECT deptno, job, COUNT(*), AVG(sal)
*
行1でエラーが発生しました。:
ORA-00979: GROUP BYの式ではありません。
なお、GROUP BY句に指定した列をSELECT句の選択リストに指定することは必須ではありません。表示結果の内容がわかりにくくなるため、あまり利用されるケースはありませんが、GROUP BY句に指定した列をSELECT句の選択リストに指定しなくてもエラーになりません。
以下の例では、GROUP BY句に指定されているDEPTNO列を、SELECT句には指定していませんが、エラーにはなっていません。
▶ GROUP BY句を使用する場合のSELECT句の選択リスト
SQL> SELECT COUNT(*), AVG(sal)
2 FROM employees
3 GROUP BY deptno;
COUNT(*) AVG(SAL)
-------- ----------
6 280833.333
5 234000
3 325000
〇 ORDER BY句とGROUP BY句を併用する場合、ORDER BY句にはGROUP BY句で指定した列とグループ関数のみ指定できる
ORDER BY句とGROUP BY句を併用する場合、ORDER BY句に指定できるのはGROUP BY句で指定した列とグループ関数のみです。他の列を指定するとエラーになります。
以下の例では、DEPTNO列の値でグループ化された各グループを、EMPNO列の値でソートしようとしていますが、各グループはEMPNO列の値ではグループ化されていないためエラーになっています。
▶ エラーになるGROUP BY句(3)
SQL> SELECT deptno 部門番号, MAX(sal) 最高給与
2 FROM employees
3 GROUP BY deptno
4 ORDER BY empno;
ORDER BY empno
*
行4でエラーが発生しました。:
ORA-00979: GROUP BYの式ではありません。
まとめ
▶ GROUP BY句を指定すると、行をグループ化できる
▶ GROUP BY句は、WHERE句の後ろ、かつORDER BY句の前に指定する
▶ GROUP BY句に列別名は指定できない
▶ SELECT句の選択リストにはGROUP BY句で指定した列とグループ関数のみ指定できる
▶ ORDER BY句にはGROUP BY句で指定した列とグループ関数のみ指定できる
▶ グループ関数は最大2レベルまでネストできる
▶ グループ関数をネストする場合は、GROUP BY句が必要
▶GROUP BY句に指定した列をSELECT句のリストに指定しなくてもエラーにはならない
