集計をしたい

質問

ＳＱＬで集計をしたいです。
ＳＱＬを始めたばかりの者です。
下記、よろしくお願いします。
説明不足がありましたら、ご指摘をお願いします。

ＭＳ－ＳＱＬＳｅｒｖｅｒ２００５環境です。

下記の売上テーブルが存在します。

テーブル：UriageTBL
年月日 受注番号 商品コード 商品名 個数　　単価　　金額
20101001　100　　　300　　　　　リンゴ　　　1　　　100　　　100
20101001　110　　　400　　　　　バナナ　　 1　　　150　　　150
20101001　110　　　400　　　　　バナナ　　-1　　　150　　-150
20101001　120　　　500　　　　　キャベツ　　1　　　120　　　120
20101002　130　　　500　　　　　キャベツ　　1　　　120　　　120
20101002　140　　　600　　　　　レタス　　　1　　　110　　　110
20101002　140　　　600　　　　　レタス　　　1　　　110　　　110
20101002　150　　　400　　　　　バナナ　　 1　　　150　　　150

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
商品コードに対応する商品グループ（テーブルは存在しません）
商品コード　商品グループ
300　　　　　果物
400　　　　　果物
500　　　　　野菜
600　　　　　野菜

－－－－－－－－－－－－－－－－－－－－－－－－－－－－
【結果】
年月日 商品グループ 受注件数 個数合計 金額合計
2010/10/01 果物 　　　　　 1　　　　　1　　　　　100
2010/01/01 野菜 　　　　　 1　　　　　1　　　　　100
2010/10/02 果物 　　　　　 1　　　　　1　　　　　150
2010/10/02 野菜　 　　　　 2　　　　　3　　　　　340

売上テーブル（UriageTBL)を、年月日別、商品グループ別に集計し、受注件数、個数合計、金額合計を取得したいです。
また、商品コードに商品グループテーブルが存在しない為、ＳＱＬ内で設定します。
ただし、受注合計金額が０円の時は集計から外します。（受注番号１１０は集計から外します。）
（受注件数は、受注番号の件数です。）

上記の場合、どのようなSQLの組み方をすればよいのか見当もつかず悩んでおります。
どなたかご教授頂ける方がいらっしゃいましたら、よろしくお願い致します。

回答No.3

taka451213 · ベストアンサー

こんばんは。

受注番号の集計がネックですね・・・。
相殺されているレコードでも、プラス側の個数が2以上で、マイナス側の個数がプラスよりも少なければ生きなので、完全に相殺されている受注番号だけ除かないといけません・・・。
同じ日付で同じグループ（野菜か果物か）の受注番号は1つとして数えますが、商品コードによってグループが変わる場合は別々に数えないといけません・・・。

手順としては、完全に相殺されているレコードを除外する。
不完全に相殺されている場合はプラス側からマイナス側の個数、金額を引いて生きとする。
グループ分けをする。
これに伴い、同一グループの同一受注番号を数えないようにフラグを立ててサマリーする。

まぁ、口で言うのは簡単なんですが、一筋縄ではいきませんねぇ・・・。
テーブルの件数はどれぐらいですか？
自己結合するので、レスポンスも心配です。
これ主キーはないんですか？

お礼

ご質問についてですが、主キーは「受注番号」・「明細番号（明細行）」です。
また、レコード件数ですが、そのテーブル自体の件数は１２万件で、実際に処理対象の件数は、４千～５千件／月程度で、最大６万件／年を見込んでます。

また、手順にもとづき自分なりにＳＱＬを組んでみます。

ありがとうございます。

回答No.4

taka451213

こんにちは。

済みません・・・、もうひとつ質問なんですが・・・。
普通は赤黒で相殺する場合、一度同一受注番号で赤伝発行して、完全に相殺してから、改めて黒伝発行すると思いますが、そのようなやり方ですか？
例えば2行目のバナナの個数が2だった場合を想定して、この注文個数を1に変更した場合、3行目の赤伝を-2個で相殺して、新たに4行目（ここにはないが・・・）で新規の受注番号でバナナ1個を計上しますか？
そのようなやり方をしている場合（普通だと思いますが・・・）は、もう少し簡単になりそうですが・・・。

お礼

taka451213様
いろいろ相談に乗って頂きありがとうございました。
何とか下記構文にて出したい結果が出ました。
１番目の方の返答を頂きました時には正直へこみましたが、taka451213様に勇気を頂き大変感謝しております。

select left(convert(varchar,年月日),4) + "/" + substring(convert(varchar,年月日),5,2) + "/" + right(convert(varchar,年月日),2) as "売上日付",
case 商品コード
when 300 then "果物"
when 400 then "果物"
when 500 then "野菜"
when 600 then "野菜"
end as "商品グループ",
count(distinct(受注番号)) as "受注件数",sum(数量),sum(金額)
from UriageTBL x right join(select 年月日,商品コード,sum(金額) from UriageTBL group by 年月日,商品コード,受注番号 having sum(金額) <> 0) y on(x.年月日 = y.年月日 and x.商品コード = y.商品コード)
group by 年月日,
case 商品コード
when 300 then "果物"
when 400 then "果物"
when 500 then "野菜"
when 600 then "野菜"
end as "商品グループ"

補足

早速のご返答ありがとうございます。

基本的な運用につきましては、おっしゃるとおりの赤黒同一伝票番号にて相殺後、新たに伝票を切り直しが原則ですが、アプリケーション上はその限りではございません。
決めうちで、運用以外のデータは無いと思いたいのですが出来る事でしたら発生しうる事は想定しておければと思いますので、よろしくお願い致します。

回答No.2

taka451213

こんばんは。

2010/01/01 野菜の金額合計は120円じゃないの？？？
あと、相殺する場合に同じ受注番号というのは理解できますが、20101002のレタスに関しては、追加注文にしろ何にしろ、受注番号は変わらないの？
同一受注なら、レコードを追加するのではなく、個数が2になると思うんですが・・・。

補足

そのとおりですね。
受注番号がおかしいのではなくて、商品番号が間違ってました。
ご指摘ありがとうございました。

テーブル：UriageTBL
年月日 受注番号 商品コード 商品名 個数　　単価　　金額
20101001　100　　　300　　　　　リンゴ　　　1　　　100　　　100
20101001　110　　　400　　　　　バナナ　　 1　　　150　　　150
20101001　110　　　400　　　　　バナナ　　-1　　　150　　-150
20101001　120　　　500　　　　　キャベツ　　1　　　120　　　120
20101002　130　　　500　　　　　キャベツ　　1　　　120　　　120
20101002　140　　　500　　　　　キャベツ　　1　　　120　　　120
20101002　140　　　600　　　　　レタス　　　1　　　110　　　110
20101002　150　　　400　　　　　バナナ　　 1　　　150　　　150

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
商品コードに対応する商品グループ（テーブルは存在しません）
商品コード　商品グループ
300　　　　　果物
400　　　　　果物
500　　　　　野菜
600　　　　　野菜

－－－－－－－－－－－－－－－－－－－－－－－－－－－－
【結果】
年月日 商品グループ 受注件数 個数合計 金額合計
2010/10/01 果物 　　　　　 1　　　　　1　　　　　100
2010/01/01 野菜 　　　　　 1　　　　　1　　　　　100
2010/10/02 果物 　　　　　 1　　　　　1　　　　　150
2010/10/02 野菜　 　　　　 2　　　　　3　　　　　350

回答No.1

SaKaKashi

丸投げですか？
仕事なのか、課題なのか。
仕事なら、その仕事は向いてないので辞めた方がいいです。
課題なら学校で聞けばすむこと。
自己啓発なら、本を買えば山のようにサンプルがありますから。

UriageTBLを年月日、商品コードでgroup byして 個数、金額を合計して、金額0は除外する。
その結果と商品グループを商品コードでOUTER JIONする。

結果例にキャベツがないが、キャベツは嫌いなのかな。

補足

丸投げですみません。
別にキャベツが嫌いなわけではありませんが、結果には商品名は出力不要なので、表示してないだけです。
ご教授通りにSQLを作ってみました。
またやる気が無いのかと言われるかもしれませんが、文献を読みましてその通りに行ってもエラーが消えませんので、再度ご指摘願います。
SQLは下記の通りです。
select left(convert(varchar,年月日),4) + "/" + substring(convert(varchar,年月日),5,2) + "/" + right(convert(varchar,年月日),2) as "売上日付",受注番号,
case 商品コード
when 300 then "果物"
when 400 then "果物"
when 500 then "野菜"
when 600 then "野菜"
end as "商品グループ",
count(*) as "受注件数",sum(数量),sum(金額)
from UriageTBL x right join(select 年月日,商品コード,count(*) as "受注件数",sum(個数),sum(金額) from UriageTBL group by 年月日,商品コード,受注番号 having sum(金額) <> 0) y on (x.年月日 = y.年月日 and x.商品コード = y.商品コード)
group by 年月日,
case 商品コード
when 300 then "果物"
when 400 then "果物"
when 500 then "野菜"
when 600 then "野菜"
end as "商品グループ"
このSQLを実行しますと、『商品コードがは集計関数または GROUP BY 句に含まれていないので、選択リスト内では無効です。』と表示されます。それで、商品コードをGROUP BY句に入れますと、商品コード単位の集計となり商品グループ単位の集計にはなりません。どこかが間違っているとは思いますが、どこが間違っているのかわかりませんので、ご指摘をお願いします。