ユニオンクエリでまとめると特定のデータが二重で表示される

2020/05/27 08:51

sousuke様閲覧ありがとうございます。 SELECT [2000年].JANコード, [2015年].商品名, Sum([2015年].販売数) AS 販売数の合計, [2000年].販売価格, Sum([2000年].合計売上) AS 合計売上の合計 FROM 2000年 GROUP BY [2000年].JANコード, [2000年].商品名, [2000年].販売価格; 2000年の部分はテーブルごとに2000年・2001年・2002年・2004年・2005年の5個分あり、 SQLビューでの表記はこの部分だけ異なり、それ以外はすべて一緒です。宜しくお願いいたします。

2020/05/27 09:53

１から３の全クエリをSQL表示にして、ここへの返答ではなく、質問の編集にてご提示いただけないと、どこがまちがっているのかの指摘ができかねます。

2020/05/27 10:00

データ例３での希望状態で、最初の同一内容の２行が問題ない理由が、現在の情報ではわかりません。

sousuke

2020/05/27 10:41

質問を編集してクエリ文を載せてください。おそらく上で提示しているのは１のクエリでしょうけど、ユニオンクエリのSQLも提示してください。

2020/05/28 01:04

YT0014様閲覧ありがとうございます。上部の「③②のクリエ5個分をユニオンクリエでまとめる」の作業をした結果のデータ表記が「データ例③での状態　※こうなってほしい例」に繋がります。例えば、JANコード1234 商品名○○がテーブル名：2000年売上の中のデータだったとして、JANコード5678 商品名●●がテーブル名：2001年売上の中のデータとします。(「データ例①での状態」に記載通り) 上部②の作業時にはデータに問題はなく、ユニオンクリエでまとめるとデータの結果が2001年売上データの中の一部が二重表記されてしまいます。

2020/05/28 01:06

sousuke様ご返信ありがとうございます。編集でユニオンクエリのSQL記載しました。集計したクエリのSQLをコピペして、改行下にUNION ALLを入力を繰り返したものがユニオンクリエのSQLになっています。

2020/05/28 02:28

例示の２(2000年)の結果が、そもそも、間違っています。正しくは、以下。 1234　○○　１０　1000　１００００ 5678　●●　　４　1000　　５０００もし、これを誤解されていたとしたら、最終結果も誤解されていませんか？

行動規範の内容に同意します

回答3件

こちらで把握した限りでは、「二重に出力される」のが正しい結果だと思います。
１行だけなのは、年単位の販売数がたまたま一致したのが１回だけだったからでは？

まずは、年毎(２)の出力結果を、実際に確認してみてください。
そして、年毎の結果から、正しい総合計を(二重になっている商品だけでも)計算してみてください。

例の場合
データ例①での状態
(2000年売上)
JANコード　商品名　販売数　販売価格(税抜)　
1234 　　　○○　　　5　　　1000
1234 　　　○○　　　5　　　1000
5678 　　　●●　　　4　　　1000

(2001年売上)
JANコード　商品名　販売数　販売価格(税抜)　
5678 　　　●●　　　5　　　1000
5678 　　　●●　　　3　　　1000

データ例②での状態(2000年売上テーブルを元に集計)
JANコード　商品名　販売数　販売価格(税抜)　合計売上(税抜)
1234 　　　○○　　 10　　　1000　　　　　　10000
5678 　　　●●　　　4　　　1000　　　　　　 5000

2001年
JANコード　商品名　販売数　販売価格(税抜)　合計売上(税抜)
5678 　　　●●　　　8　　　1000　　　　　　 8000

データ例③での状態　クエリの結果
JANコード　商品名　販売数　販売価格(税抜)　合計売上(税抜)
1234 　　　○○　　 10　　　1000　　　　　　10000　＜＜2000年
5678 　　　●●　　　4　　　1000　　　　　　 5000　＜＜2000年
5678 　　　●●　　　8　　　1000　　　　　　 8000　＜＜2001年

なお、個人的には、hatena19さんの回答のようなデータ構造にするのをお勧めします。
そうしておけば、2010年から７年分とかの集計が必要になっても、条件欄の変更のみで対応可能になります。

投稿2020/05/28 02:32

編集2020/05/28 02:46

総合スコア1750

2020/05/28 02:50

なお、二重計上か否かが不明瞭なのは、集計時に年データを付記していないのが原因です。別年であれば、二重計上ではないのが明確になります。

2020/05/28 05:12

ありがとうございます。原因の解決ならびに作成方法の改善についてご教示いただきました方法にて作成してみようと思います。ご丁寧な回答ありがとうございました。

行動規範の内容に同意します

ユニオンクエリで元のデータ件数の合計からデータ件数が減る場合はあっても、増えることはありません。
もし、増えているなら、ユニオンクエリにする前の５つのクエリですでに増えているはずです。
もとの５つのクエリを確認してみてください。
わからなければ、もとの５つのクエリのSQLを提示してください。

質問の内容とは少しずれますが、

①Excelからコピーしてきた売上データ5個分のテーブル

例）2000年売上　20001年売上・・・

この出発点から、データベースの設計としては間違っています。そのため、複雑なこと（ユニオンクエリとか）になってます。複雑になりますし、処理速度が遅くなる原因にもなります。

５つのテーブルを作るのではなく一つのテーブルに追加するようにします。そのとき、「年度」フィールドを追加しておきます。

データ例① なら、下記のような一つのテーブルにします。

年度　JANコード　商品名　販売数　販売価格(税抜)　
－－－－－－－－－－－－－－－－－－－－－－－－－－
2000　1234　　　　○○　　　5　　　　1000
2000　1234　　　　○○　　　5　　　　1000
2000　5678　　　　●●　　　4　　　　1000
2001　5678　　　　●●　　　5　　　　1000
2001　5678　　　　●●　　　3　　　　1000

こうしておけば、ユニオンクエリを使う必要はなく、単純な集計クエリで集計できます。

投稿2020/05/28 01:47

hatena19

総合スコア34075

2020/05/28 02:05

hatena19様ご回答ありがとうございます。なるほどですね、ありがとうございます。今後も継続して今回作成したものを元にテーブルを追加していき、更新をしていこうと思っていたので、二重表記される原因がわからないことには今後も使うためにこのままでいいのか…という疑問もありますので、もし解決しない場合はhatena19様のご意見参考に作り直しをしようと思います。

hatena19

2020/05/28 03:10

「今回作成したものを元にテーブルを追加していき、」だと、追加するたびにSQLを書き直す必要がでてきます。テーブルを追加するのではなく、一つのテーブルにデータを追加するという設計にすべきです（強く断言）。件数が増える（二重表記）に関しては、ユニオンクエリにする前のクエリのデータを確認してください。ユニオンクエリの問題ではないはずです。

2020/05/28 05:11

ありがとうございます。ひとつのテーブルにデータを追加していった方が簡単なんですね。また、今回の原因について解決しましたので、ご教示いただいた方法でデータの作成をしてみようと思います。ご丁寧な回答ありがとうございました。

行動規範の内容に同意します

ベストアンサー

4個分のクリエのデータは二重表示なしなのに、1個分のクリエデータが二重表示されてしまう

先ず、ユニオンクエリーですが、unionでしょうかunion allでしょうか？

unionの場合は、項目の一部が違うだけでも（例えば商品名の後ろに空白の有り無しとか）別なものとして表示されます。

union allの場合は、そういったデータが別なクエリーに存在しているという事です。

2重表示されるものが分かっているなら、それぞれのクエリーでのデータ詳細を確認して下さい。

追記

元にしているデータのレイアウトが変わらないなら、union allするのはデータをマージするのを先に行うと簡潔です。

SQL
1SELECT JANコード, 商品名, 販売価格
2     , Sum(販売数) AS 販売数の合計, Sum(合計売上) AS 合計売上の合計
3from (
4                select * from [2000年]
5      union all select * from [2001年]
6      union all select * from [2002年]
7      union all select * from [2003年]
8      union all select * from [2004年]
9) as marge
10GROUP BY JANコード, 商品名, 販売価格

unionの部分で、元の年度を識別するようには以下の様にします。

SQL
1                select 2000 as 年度, * from [2000年]
2      union all select 2001, * from [2001年]
3      union all select 2002, * from [2002年]
4      union all select 2003, * from [2003年]
5      union all select 2004, * from [2004年]

追記

本来
フィールド名：JANコード　商品名　販売数　販売価格(税抜)　合計売上(税抜)
1234　　　　○○　　　10　　　　1000　　　　　　　10000
5678　　　　●●　　　12　　　　1000　　　　　　　12000
となった最終データがほしいのに
フィールド名：JANコード　商品名　販売数　販売価格(税抜)　合計売上(税抜)
1234　　　　○○　　　10　　　　1000　　　　　　　10000
5678　　　　●●　　　20　　　　1000　　　　　　　20000
となる

上記で言う所のJANコード「5678」のデータを見て下さい。
上記の結果になるなら、「数量」に値があり「売上」が0のデータがあるはずです。

投稿2020/05/28 00:48

編集2020/05/28 02:31

総合スコア25327

2020/05/28 01:11

sazi様ご回答ありがとうございます。 UNION ALLを使用しています。それぞれのクエリも確認しましたが、二重で表示される売上データは1つしか存在しません。今回の例でいうと、2000年・2002年～2005年には JANコード5678・商品名●●は同じ数量での売上データが存在せず、2001年の売上テーブルにのみデータがあります。

2020/05/28 01:26 編集

質問に追記したユニオンクエリーの内容ですけど、実際に動作させているものにして下さい。 ;があったりとか、販売価格でもgroup byしているとか、？な部分がありますので。

2020/05/28 01:37

動作させているものを[]のテーブル名を例としてあげているものに変更しているのみで、それ以外に変更はなく、実際にSQLビューに表示されているままとなっております。販売価格は名前の通り商品1個あたりの販売価格を表示しており、販売数によって変更があるものではありませんのでJANコードや商品名と同じくグループ化をさせています。集計したいのは販売数と合計売上のみです。([販売数]*[販売価格]で合計売上を計算)

2020/05/28 01:41

> 実際にSQLビューに表示されているまま from句にないテーブル名（ [2015年].商品名）とか、unionの前に;があったりしますので、そもそもエラーになると思いますけど

2020/05/28 01:45

因みに販売価格が年で変更になっていると、別なデータとして表示されますけど、販売価格には変動はないのでしょうか？

2020/05/28 01:56

失礼いたしました。SQL修正しました。 [2015年].商品名は実際のデータですので実際には問題ないです。 (ここでは2000年に修正しました) ;がついてる点についてですが、SQLビューに表示されているものをコピーして[]のみ修正、それ以外の半角スペース含め;などもいじっておりませんのでデータのままです。ちなみに、二重表示されてしまうデータがあるテーブル以外はユニオンクリエで実行した際に問題なく表示されているので、なぜエラーになるとおっしゃっているのかが分からず… 同じ商品でも販売価格が違う商品があり、そういったものは別で集計されてほしいので、一部変動はありますが、別データとしての表示で問題ありません。今回は販売価格も同じでテーブル・テーブルを集計したクリエの状態までは該当のデータは１つしかないのに、ユニオンクリエを実行すると二重表示されてしまう件についての解決策を探しています。

2020/05/28 02:02

;については失礼しました。 ;はセンテンスの終わりを示すもので、本来そこで一文としてのSQLは終わりです。 unionの場合は確かにエラーになりませんね。

2020/05/28 02:07 編集

後半に示したSQL（unionのみの部分）を作成して保存して下さい。その上でそのクエリーに対して重複となっているJANコードを抽出するとどうなるか確認下さい。先ず、異なる年度のデータがいないかどうかを確認して下さい。

2020/05/28 02:08

ありがとうございます。こちらこそせっかくご助言いただいているのにお話すべてを理解できておらず恐縮です。ご提案いただいた内容で一度作成してみて、またご報告させていただきます。引き続き、何卒宜しくお願い申し上げます。

2020/05/28 02:20 編集

回答に追記しました。

2020/05/28 02:32

追記ありがとうございます。「unionの部分で、元の年度を識別するようには以下の様にします。」の下にあるSQLで作成しましたところ、2003年(仮)のデータに問題の二重表記になっているデータが存在していました。ややこしい言い方できちんと伝わるか不安ですが… 2001年のテーブルを集計したデータが2003年のテーブル(集計前の元データ)に入っておりました。そもそもこうなった原因は不明ですが、Accessを初めて使用しましたので、自覚なく保存をしていたのかもしれません。テーブルのデータを確認した際にJANコードをフィルタで確認したのですが、数値フィルタに該当のJANコードがなかったため、この現象を見落としておりました。テーブルをいちから作り直し、またご報告させていただきます。

2020/05/28 03:01 編集

今回のようなunionは正規化を疑似的に行うものです。テーブルを正規化すればunionは不要になりますし、データチェックも容易になりますので、お勧めします。正規化したテーブルに変更する場合、年度を含めたunionクエリーを元にテーブル作成クエリーで行うと効率的です。