集計クエリについて

質問

注文データ
・注文番号(重複なし)

商品テーブル
・商品コード
・商品名

実績1
・注文番号
・商品コード
・出庫日
・使用重量

実績2
・注文番号
・商品コード
・出庫日
・使用重量

上記4つのテーブルがあり、それぞれ下記の内容が入っています。

注文データ
A01
A02
A03
A04

商品テーブル
S01 あずき
S02 いちご
S03 りんご

実績1
A01 S01 2015/9/1 10.00
A02 S02 2015/9/2 10.00
A04 S01 2015/9/4 10.00

実績2
A01 S01 2015/9/1 10.00
A03 S03 2015/9/3 10.00
A04 S01 2015/9/4 10.00

クエリを使用して実績1と実績2を下記のように集計するにはどのようにしたら良いでしょうか。
一つのクエリ内で処理したいです。

集計クエリ結果
S01 あずき 20.00
S02 いちご 10.00
S03 りんご 10.00

回答No.2

m3_maki · ベストアンサー

実績1、実績2 に共通する注文番号は、重複データとして
１件のみ集計の対象とする、といことですね？

同じ注文番号で、違う値のフィールドがあると
重複して集計されるので
もう一工夫する必要があります。

SELECT T.商品コード, T.商品名, Sum(Q.使用重量) AS 使用重量の合計
FROM 商品テーブル AS T
INNER JOIN
(SELECT *
FROM 実績1
UNION
SELECT * FROM 実績2) AS Q
ON T.商品コード = Q.商品コード
GROUP BY T.商品コード, T.商品名

実績のない商品もすべて表示したい場合は
INNER　JOIN
を
LEFT JOIN
に変えてください。

> 一つのクエリ内で処理したいです。
ということなので、サブクエリを使い、分かりにくいものになっています。

お礼

有難うございます。

そういえばUNIONがありましたね。
SQLを手打ちするのが面倒なのでクエリで楽してましたが、やはりダメですね。

>ということなので、サブクエリを使い、分かりにくいものになっています。
いえいえ、全然分かりにくくないです。

回答No.3

山田 太郎（@f_a_007）

＞S01 あずき 20.00

って、添付図のようなことだったとは・・・。

さて、そういう集計が成立するには、実績の重複が商品、使用数量まで完全に同じということ。でないと変なことになるかと・・・。だったら、合計を重複数で除した結果が求めるもの。添付図は、そういう考えに基づいています。

補足

おそらく読解力の問題かと。

回答No.1

山田 太郎（@f_a_007）

SELECT
　　実績.商品コード,
　　商品テーブル.商品名,
　　Sum(実績.使用数量) AS 使用数量の合計
FROM 実績
　　LEFT JOIN 商品テーブル
　　ON 実績.商品コード = 商品テーブル.商品コード
GROUP BY 実績.商品コード, 商品テーブル.商品名;

一般的には、このようなSQLになると思いますよ。この場合は、実績テーブルを次のように加工しています。

【実績】

注文番号
区分　<------- 1=実績1、2=実績2
商品コード
出庫日
使用数量

PS、現行のテーブル構造のままですと　DSUM()を使えば・・・。

SELECT
　　商品テーブル.商品コード,
　　商品テーブル.商品名,
　　DBLookup("SELECT SUM(使用数量) FROM 実績1 WHERE 商品コード='" & [商品コード] & "'") AS 実績1_使用数量,
　　DBLookup("SELECT SUM(使用数量) FROM 実績2 WHERE 商品コード='" & [商品コード] & "'") AS 実績2_使用数量
FROM 商品テーブル;

ここではSQL文を判りやすくするために自作のDBLookup()を使っていますがDSum()で書けます。

[実績1_使用数量]+[実績2_使用数量] AS 合計使用数量

でも、冒頭のやり方がお薦めです。現行のテーブル設計は、色々と問題が多いように思います。

※もうすぐ70歳を迎える爺の感想。無視してもらっても結構ですよ。

補足

有難うございます。

「あずき」の合計は「40」ではなく「20」です。