グループ関数
これまでは単一行関数でした。この章では以前の章でも簡単に触れましたが、複数行のデータを処理し1つのデータを返す関数です。
SELECT句、ORDER BY句、後述のHAVING句で使えます。
以下が基本構文です。
関数名( [DISTINCT | ALL] { 列名 | 式 } )
覚えてますか?DISTINCTは重複削除ですよね。なので、グループ関数においてDISTINCT指定の場合は重複した値については一度だけ処理する、ということになります。
ALLは重複含む全てを処理します。
COUNT
COUNT( { * | [DISTINCT | ALL]式 } )
取り出されたデータ件数を戻します。
引数 | 説明 |
* | 重複値、NULLの全てのデータを含む件数 |
DISTINCT 式 | 重複値は1度だけカウント、NULLは含まない |
ALL 式 | 重複値もそれぞれ1件、NULLは含まれない |
つまり、*を指定しない限りグループ関数はNULLを含みません。
これはCOUNTに限らずグループ関数の基本的な考え方です。
MAX・MIN
MAX( [DISTINCT | ALL]式 ) MIN( [DISTINCT | ALL]式 )
最大値、最小値を戻します。
AVG・SUM
AVG( [DISTINCT | ALL]式 ) SUM( [DISTINCT | ALL]式 )
平均値、合計値を戻します。
グループ化
GROUP BY
SELECT文にGROUP BYを指定すると、行をグループ化できます。
WHERE句の後ろ、ORDER BYの前、と位置が決まっています。
SELECT 列名, グループ関数( 列名 ) FROM テーブル名 [WHERE 条件] [GROUP BY グループ化する列のリスト] [ORDER BY 列]
GROUP BY 列名 [,列名 …]
GROUP BYを使う時は…
・GROUP BY句には1つ以上の列を指定
・列別名は指定できない
・SELECT句にはGROUP BYで指定した列とグループ関数のみ指定可能
・ORDER BYも上記SELECTと同条件
となっています。
ちょっと例を見てみましょう。
SELECT deptno, count(*), avg(sal) FROM EMPLOYEES GROUP BY deptno;
結果のイメージできますか?以下の様な出方をします。
10 3 325000 20 5 234000 30 6 280833.3333333333
deptnoでグループ化されて、その際のカウント数、給与の平均値が出力されていると思います。
では、GROUP BY句の後ろに列を複数指定するとどうなると思いますか?
SELECT deptno, count(*), avg(sal) FROM EMPLOYEES GROUP BY deptno, job;
前にも似たようなことをやっているので、分からない人は探してみましょう。
HAVING
グループ関数はWHERE句に指定出来ません。
グループ関数を条件にしたい場合は、HAVING句を使います。
使う場所は、WHERE句の後ろ、ORDER BYの前とGROUP BYと同じです。
GROUP BYとHAVINGは順不同です。
当然ですが、HAVINGで使えるのはGROUP BYで指定した列のみです。
SELECT 列名, グループ関数( 列名 ) FROM テーブル名 [WHERE 条件] [GROUP BY グループ化する列のリスト] [HAVING グループ化に対する条件] [ORDER BY 列]
例を見てみましょう。
SELECT deptno, avg(sal) FROM EMPLOYEES GROUP BY deptno HAVING deptno >= 20 ;
解説は不要でしょう。
以下をやっておきましょう。
以下のデータは、EMPLOYEESテーブルから取得してください。 ・営業部は何人いるか人数を出力 ・最高給与額を出力 ・最低給与額を出力 ・給与の平均を出力 ・給与の合計値を出力 ・deptnoが20以上の役職者のみで絞り、その人数と平均給与を出力