#710
あるテーブルの全レコードを出力するとともに、同じテーブルのあるフィールドをキーにした各グループの平均値も各行に出力、さらにそれらの差異を求めるクエリの作り方の例です。
このような処理を行いたい場合、平均値だけを取り出すクエリを作ったあと、元となるテーブルとそのクエリを結合する、いわゆる”クエリのクエリ”のような方法もありますが、ここでは単一のクエリで出力する方法を説明します。
まず、元となっているテーブルには下記のような構造でデータが保存されているものとします。ここでは同じ商品コードのレコードが複数登録されています。
このテーブルにおいて、「商品コード」でグループ化を行い、それぞれの商品コードの「数量」の”平均値”も各レコードに出力するようにします。
- ここではまず、『商品コードでグループ化、数量の平均を求めるクエリ』を考えます。仮に単独のクエリで作るとすると次のようなものとなります（仮なので保存する必要はありません）。
SELECT 商品コード, Avg(数量) AS 平均数量
FROM 受注明細
GROUP BY 商品コード
- 次に、最終的に保存するクエリを新規作成し、まずは上記のテーブルの全レコードの任意のフィールドを作るクエリを作成します。
SELECT ID, 受注ID, 商品コード, 数量
FROM 受注明細
- SQLビューに切り替え、このクエリのSQL文を次のように書き換えます（このクエリは必要に応じて保存します）。
SELECT ID, 受注ID, 受注明細.商品コード, 数量, 平均数量, 数量 - 平均数量 AS 差異
FROM 受注明細
INNER JOIN
(SELECT 商品コード, Avg(数量) AS 平均数量 FROM 受注明細 GROUP BY 商品コード) AS 平均Data
ON 受注明細.商品コード = 平均Data.商品コード
■ポイント
- まず、２で作ったクエリのSQL文に対して、受注明細テーブルから取り出すフィールドは同じなので、SELECTやFROMの部分はそのまま残します。
- 出力するフィールドとして「平均数量」および「差異」を追加するので、SELECTの後ろにそれらを追記します（「差異」にはその演算式を設定します）。
, 平均数量, 数量 - 平均数量 AS 差異
- 次に、１のSQL文を”サブクエリ”としてJOINします。
１のSQL文をそのままペーストしたあと、”サブクエリ”なのでそれ全体をカッコで囲みます。さらにサブクエリ内のフィールドを参照できるよう、そのサブクエリに任意の”別名”を付けておきます（ここでは「平均Data」）。
INNER JOIN
(SELECT 商品コード, Avg(数量) AS 平均数量 FROM 受注明細 GROUP BY 商品コード) AS 平均Data
- 最後に、メインとなる「受注明細」とサブクエリの「平均Data」の結合条件を追記します（ここでは両者の「商品コード」が同じレコードを結合）。
ON 受注明細.商品コード = 平均Data.商品コード
実行例：
※なお、ここで作ったクエリのデザインビューを表示すると下図のようになります。最終的に出力するフィールドや演算式などはここで編集可能ですが、サブクエリ内の構造はここでは変えられないので、基本的にはSQLビューで編集することになります。
