集計結果で絞り込む HAVING句

集計結果に対して絞り込み条件を設定するにはHAVING句を利用します。

HAVING句の構文

抽出条件として、WHERE句と同様の各種条件式を記述することが出来ます。
多くの入門書では、HAVING句は必ずGROUP BY句と組み合わせて使うかのように記載されているのですが、実はGROUP BY句なしにHAVING句を用いても文法違反にはなりません。
WHERE句とHAVING句の違いは、評価されるタイミングです。

以下のような、ここまでに学んだ句をすべて使ったSQLを考えます。

このSQLの実行順序は以下のようになります。

１．テーブルから、WHERE句の条件式1を満たすレコードが抽出される。
２．1で抽出されたレコードがGROUP BY句のカラム名でグループ化される
３．2でグループ化されたグループごとに関数の値が求められる
４．3の結果からHAVING句の条件式2を満たすものが抽出される

つまり、
最初にテーブルから操作対象となるレコードを抽出する条件を記述するには WHERE句
グループ化や関数による計算の結果について抽出条件を記述するには HAVING句
と使い分けているのです。

HAVING句の例

例1

SELECT subject, AVG(score) AS average FROM examination 
        GROUP BY subject HAVING average>=70;

科目（subject）ごとに平均点（scoreの平均値）を求め、それが70以上のものだけを抽出しています。

実行結果

例2

HAVING句の条件式にグループ関数を書くことも出来ます。

SELECT subject, AVG(score) AS average FROM examination 
        GROUP BY subject HAVING AVG(score)>=70;

実行結果は例1と同じです。

例3

グループ化も計算も行わない場合はWHERE句とHAVING句のどちらを用いても結果に差が出ません。

SELECT student_id,subject,score FROM examination WHERE score>=70;

または

SELECT student_id,subject,score FROM examination HAVING score>=70;

実行結果

2つのSQLは同じ結果になります。
（結果が同じになるとはいえ、通常このような場合はWHERE句を使います）

例4（WHERE句では不適切な例）

SELECT subject AS `科目`, AVG(score) AS average FROM examination 
        GROUP BY subject WHERE average>=70;

このように記述すると文法違反になります（WHERE句はGROUP BY句より後には書けません）
※phpmyadminでは日本語のカラム名を使うとエラーが表示されないことがあるという不具合があるので｀平均点｀ではなくaverageとしています

例5（WHERE句では不適切な例）

SELECT subject AS `科目`, AVG(score) AS average FROM examination 
        WHERE average>=70 GROUP BY subject;

この例では文法違反にはなりませんが、WHERE句のaverageというカラム名が未定義であるというエラーになります。WHERE句の実行順序がグループ化・averageの計算よりも前になるためです。

練習問題

テーブルexaminationについて、以下の内容を求めるSQLを記述しなさい。

１．最高点（scoreの最大値）が80点以上であった科目（subject）

２．受験者数（レコード数）が4人に満たなかった科目（subject）