集計表の外部結合

質問

SQL文について勉強中ですが、集計のやり方が分からないためお教えください。

SQL SERVER2008r2

Management StudioでSQLを書いてます。

◎TM部門（店舗マスタ）
店舗CD
店舗名

◎TM店舗部門（店舗部門マスタ）
店舗部門CD
店舗部門名

◎TT売上伝票（売上伝票）
伝票番号
行
店舗ＣＤ
店舗部門CD
売上日
売上時間
商品CD
商品名
数量
税抜単価
税込単価
税抜金額
税込金額
消費税
伝票合計

上記テーブルがあり、日付を限定して集計したいのですが、うまくSQLが書けません。

【希望結果例】
集計期間：2014/04/20　～ 2014/05/20

日付　　　　 　｜　店舗名　｜店舗部門名　　｜　　日別合計
2014/05/21　｜　店舗Ａ　　|　店舗部門A　　 ｜ sum(伝票合計)
2014/05/21　｜　店舗A　　|　店舗部門Ｂ　　 ｜ sum(伝票合計)
2014/05/21　｜　店舗Ａ　　|　店舗部門Ｃ　　 ｜ sum(伝票合計)
2014/05/21　｜　店舗Ｂ　　|　店舗部門Ｄ　　 ｜ sum(伝票合計)
2014/05/21　｜　店舗Ｂ　　|　店舗部門Ｅ　　 ｜ sum(伝票合計)
2014/05/21　｜　店舗Ｂ　　|　店舗部門Ｆ　　 ｜ sum(伝票合計)
　　　　　　　　　　　　　　　　　　　～

SELECT
伝票日付,
店舗CD,
店舗部門CD,
SUM(伝票合計) AS 日別合計
FROM TT売上伝票
GROUP BY 伝票日付,店舗CD,店舗部門CD
HAVING 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
ORDER BY 伝票日付,店舗CD,店舗部門CD

上記の結果
日付　　　　 　｜　店舗CD　　　｜店舗部門CD　 　｜　　日別合計
2014/05/21　｜　店舗Ａ　　　　｜　店舗部門A　　 ｜ sum(伝票合計)
2014/05/21　｜　店舗A　　　　｜　店舗部門Ｂ　　 ｜ sum(伝票合計)
2014/05/21　｜　店舗Ａ　　　　｜　店舗部門Ｃ　　 ｜ sum(伝票合計)
2014/05/21　｜　店舗Ｂ　　　　｜　店舗部門Ｄ　　 ｜ sum(伝票合計)
2014/05/21　｜　店舗Ｂ　　　　｜　店舗部門Ｅ　　 ｜ sum(伝票合計)
2014/05/21　｜　店舗Ｂ　　　　｜　店舗部門Ｆ　　 ｜ sum(伝票合計)

集計は全く問題ないのですが、ここからリレーションし、店舗名、店舗部門名を表示したいのですが、書き方が分かりません。

無知ながら、下記方法を試しました結果
SELECT
TT売上伝票.店舗CD,
TM店舗.店舗名,
TT売上伝票.店舗部門CD,
TM店舗部門.店舗部門名
FROM TT売上伝票
JOIN TM店舗
ON TM店舗.店舗CD=TT売上伝票.店舗CD
JOIN TM店舗部門
ON TM店舗部門.店舗部門CD=TT売上伝票.店舗部門CD
UNION ALL
SELECT
伝票日付,
店舗CD,
店舗部門CD,
SUM(伝票合計) AS 日別合計
FROM TT売上伝票
GROUP BY 伝票日付,店舗CD,店舗部門CD
HAVING 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
ORDER BY 伝票日付,店舗CD,店舗部門CD

エラーメッセージ
ステートメントが UNION、INTERSECT、EXCEPT のいずれかの演算子を含んでいるときは、選択リスト内に ORDER BY 項目が必要です。

無知ながら、UNION演算子でやる方法と、サブクリエを使う方法があるのではと思いますが、出来れば利点と欠点を含めてお教え頂けたら幸いです。

回答No.1

Siegrune · ベストアンサー

まず、問題はないというところから。。。

＞SELECT
＞伝票日付,
＞店舗CD,
＞店舗部門CD,
＞SUM(伝票合計) AS 日別合計
＞FROM TT売上伝票
>GROUP BY 伝票日付,店舗CD,店舗部門CD
＞HAVING 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
＞ORDER BY 伝票日付,店舗CD,店舗部門CD

HAVING句は抽出結果に対して処理されます。
ということで、１０年分データがあったら、１０年分の全件集計した結果に対して
伝票日付の条件にあうデータを探します。⇒非常に遅い。
普通は、WHERE句を使います。（抽出対象に対して処理されます）
WHERE 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
という感じです。⇒
何年分のデータがあろうが、１か月分を対象として抜き出してから集計されます。
(group by の手前に書いてください)

＞リレーションし、店舗名、店舗部門名を表示したいのですが、書き方が分かりません。
Unionについてから。
unionは複数のselectの結果を合わせて出力するときに使います。
設計的には不適切ですが、例えば、１週単位で別のテーブルに持っているなら

SELECT 伝票日付,店舗CD,店舗部門CD,SUM(伝票合計) AS 日別合計
FROM TT売上伝票４月第３週
WHERE 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
GROUP BY 伝票日付,店舗CD,店舗部門CD
union
SELECT 伝票日付,店舗CD,店舗部門CD,SUM(伝票合計) AS 日別合計
FROM TT売上伝票４月第４週
WHERE 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
GROUP BY 伝票日付,店舗CD,店舗部門CD
union
・・・
という風に記述して、４月第３週から５月第２週までのデータをそれぞれのテーブルから取得します。
（例が悪いと思っている理由は、通常は年単位のテーブルや別々の種類のテーブルから
　unionするのが一般的なため。）

で、望みのものは、
SELECT
d.伝票日付,
d.店舗CD,
t.店舗名,
d.店舗部門CD,
b.店舗部門名,
SUM(d.伝票合計) AS 日別合計
FROM TT売上伝票 d
JOIN TM店舗 t
t.店舗CD=d.店舗CD
JOIN TM店舗部門 b
b.店舗部門CD=d.店舗部門CD
where 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
GROUP BY 伝票日付,店舗CD,店舗部門CD
ORDER BY 伝票日付,店舗CD,店舗部門CD

と、結合した結果を集計するか、

SELECT
x.伝票日付,
x.店舗CD,
t.店舗名,
x.店舗部門CD,
b.店舗部門名,
x.日別合計
FROM
(
SELECT 伝票日付,店舗CD,店舗部門CD,SUM(伝票合計) AS 日別合計
FROM TT売上伝票
WHERE 伝票日付 BETWEEN '2014-04-21' AND '2014-05-20' AND 店舗CD IN(1,2,3,4)
GROUP BY 伝票日付,店舗CD,店舗部門CD
) x
JOIN TM店舗 t
t.店舗CD=x.店舗CD
JOIN TM店舗部門 b
b.店舗部門CD=x.店舗部門CD
ORDER BY x.伝票日付,x.店舗CD,x.店舗部門CD

と集計した結果にJOINするかします。

お礼

Siegrune様
詳しい回答ありがとうございます。
まさに、私が知りたかった事でした。

また、GROUP BY（集計）から、抽出の場合は必ず "HAVING"で行うと思っていたので、先に"WHERE"で抽出できるとは考えてもいませんでした。
あわせて、"FROM"以外でも、エイリアスがかけるという事も知りませんでした。

お教えいただいた内容を、よく理解し今後の参考にさせて頂きます。

ありがとうございました。