前回は基本的なグループ関数について学んできましたが、今回はその応用として、
GROUP BY句を学んでいきます。
グループ関数にはMAX,MIN関数など最大値や最小値を求める関数がありました。
例えば以下ような問い合わせはどうするでしょうか。
各部門ごとの最大の給与や平均給与を求めたい。
この場合はグループ関数だけでなく、GROUP BY句を使用し、
各部門でグループ化を行う必要があります。
グループ化
今回のemployees表の場合、DEPT_IDがで従業員の部門がどこか確認できます。
つまり部門ごとの平均給料を求めたい場合は、
DEPT_IDが同じ値の行をグループにし、グループごとの最大、平均を求めます。
グループごとにグループ関数を処理するときは、GROUP BY句を使用します。
構文は以下の通りです。
[構文] GROUP BY句
SELECT 列名, 列名…
FROM 表名
[WHERE 条件式]
GROUP BY 列名, 列名…
[ORDER BY 列名];
SELECT 列名, 列名…
FROM 表名
[WHERE 条件式]
GROUP BY 列名, 列名…
[ORDER BY 列名];
ORDER BYは一番最後となります。
順番を間違えてしまうと構文エラーとなるので注意しましょう
では、部門ごとの年収の最大を確認してみましょう。
SQL> SELECT dept_id,max(salary) FROM employees 2 GROUP BY dept_id; DEPT_ID MAX(SALARY) ---------- ----------- 10000000 50 5500000 30 6400000 40 6700000 20 8000000 10 6000000 6 rows selected.
上記例ではGROUP BY句にDEPT_IDが指定されています。
その為、DEPT_IDが同じ値でグループ化を行い、各部門ごとの最大給与を表示しています。
このようにGROUP BY句を使用することで指定した列で
グループ化を行うことでより柔軟な検索を行うことができるようになります
また、GROUP BY句には複数列を指定することもできます。
複数列を指定した場合、その列の組み合わせで同じ行がグループ化されます。
複数列でグループ化する場合
では実行例です。
SQL> SELECT hire_date,dept_id,max(salary) FROM employees 2 GROUP BY hire_date,dept_id; HIRE_DATE DEPT_ID MAX(SALARY) --------- ---------- ----------- 29-SEP-03 30 5400000 01-MAR-01 30 6400000 28-JUL-03 30 5400000 01-APR-13 50 4000000 01-APR-98 10 6000000 01-APR-13 07-OCT-92 10000000 11-MAR-95 10 5000000 22-FEB-01 30 5400000 01-APR-13 40 4000000 01-APR-13 30 4000000 11-OCT-92 20 8000000 01-APR-09 20 6500000 01-APR-97 30 5400000 14-MAY-98 50 5500000 01-OCT-00 40 6700000 16 rows selected.
GROUP BY句を使用したよくあるエラー
では続いてGROUP BY句でよく起こる構文エラーについて確認してきます。
以下の構文はエラーになります。
SQL> SELECT dept_id as id,max(salary) FROM employees 2 GROUP BY id; GROUP BY id * ERROR at line 2: ORA-00904: "ID": invalid identifier
上記例では、DEPT_IDに列別名idを指定し、GROUP BYに列別名idを指定しています。
ORDER BY句では列別名を使用できますが、GROUP BY句に列別名は使用できないため、
エラーになります。間違えやすいのでご注意ください。
続いて以下の構文もエラーになります。
SQL> SELECT hire_date,dept_id,max(salary) FROM employees 2 GROUP dept_id; SELECT hire_date,dept_id,max(salary) FROM employees * ERROR at line 1: ORA-00979: not a GROUP BY expression
上記例では、hire_dateがGROUP BY句に含まれていないというエラーになります。
SELECT句で指定したグループ関数以外の列は、
すべてGROUP BY句で指定しないとエラーになります。
今回の実行例では、SELECT句にhire_date,dept_idを指定しているため、
GROUP BYにはhire_dateとdept_idを指定する必要があります。
では最後に正しい構文で実行した結果です。
SQL> SELECT hire_date,dept_id,max(salary) FROM employees 2 GROUP BY hire_date,dept_id; HIRE_DATE DEPT_ID MAX(SALARY) --------- ---------- ----------- 29-SEP-03 30 5400000 01-MAR-01 30 6400000 28-JUL-03 30 5400000 … 16 rows selected.
今回はここまでとなります。次回はグループ関数を使用した制限を学んでいきます。