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

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

概要

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

エラーメッセージ

原因

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

例

以下のテーブルの場合

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

SELECT category, AVG(price) AS average FROM ProductsA WHERE AVG(price) > 500 GROUP BY category

avg()による平均値の算出は、GROUP BYの処理後に実行されますが、WHEREの条件式は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 の利用」の記事も参照してください。

結果

サブクエリにする

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

SELECT * FROM (
  SELECT category, AVG(price) AS average FROM ProductsA GROUP BY category) AS a
WHERE average > 500

著者

neg

iPentec.com の代表。ハードウェア、サーバー投資、管理などを担当。
Office 365やデータベースの記事なども担当。