where節に集計関数の条件を記述するとエラーになる - SQL

SQLのwhereに集計関数を記述するとエラーになる場合の対処方法を紹介します。

概要

SQLのwhere節に集計関数を用いた条件式を記述すると、実行時に下記のエラー「集計が HAVING 句または選択リスト内のサブクエリにある場合、および集計する列が外部参照の場合にだけ、WHERE 句に集計を含めることができます。」が発生します。

エラーメッセージ

原因

whereの条件がgruop byの実行前に判定されるため、正しく実行できない可能性があります。

例

以下のテーブルの場合

ProductsA テーブル

id	name	price	category
1	Penguin	250	Bird
2	Bear	1050	Mammal
3	Duck	150	Bird
4	Camel	550	Mammal
5	Owl	185	Bird
6	Whale	880	Mammal

このとき、下記のSQLを実行するとエラーになります。

select category, avg(price)as average from ProductsA where avg(price) > 500 group by category

avg()による平均値の算出は、group byの処理後に実行されますが、wheweの条件式はgroup by の処理前に実行されるため、avg()の平均値の算出ができないため実行エラーになります。

対策

havingを利用する

having句を利用すると、group byでの集計(グループ化)が終わった後で条件を指定できます。
先のエラーのコードは以下のコードにすると実行できます。

select category, avg(price)as average from ProductsA group by category having avg(price) > 500

having句の利用は「group by での集計結果に対して条件を指定して絞り込む - having の利用」の記事も参照してください。

結果

	category	average
1	Mammal	826.666666

サブクエリにする

あまり美しくはないですが、group byでの集計SQLをサブクエリにして、メインのクエリでwhereで条件指定する方法でも実行できます。

select * from (
  select category, avg(price)as average from ProductsA group by category )as A
where average > 500