GROUP BYでまとめたデータをさらにSUMで合計する

Question

環境　VS2010　SQLServer2008　ＶＢ　ASP.NET4

テーブル

店舗　　　エリア　　　　　固定商品数　　　使用者数　　　使用日

A　　　　　　　1　　　　　　　　5　　　　　　　　2　　　　　　2012/4/26

B　　　　　　　1　　　　　　　　6　　　　　　　　4　　　　　　2012/4/26

A　　　　　　　1　　　　　　　　5　　　　　　　　3　　　　　　2012/4/26

B　　　　　　　1　　　　　　　　6　　　　　　　　2　　　　　　2012/4/26

というテーブルがあるとして、｛（店舗、エリア、固定商品数）テーブル+（使用者数、使用日）テーブル｝

以下のようviewを作成したい（エリアごとの固定商品数に対する使用者割合を出したい為）

エリア　　　固定商品数計　　　使用者合計数　　　使用日

1　　　　　　　　11　　　　　　　　11　　　　　　　　　2012/4/26

SELECT            tbl1.使用日, SUM(tbl1.使用者数) AS 使用者合計数, SUM(tbl_shop.固定商品数) AS 固定商品数計, tbl_area.エリア
FROM              tbl_shop INNER JOIN
                        tbl_area ON tbl_shop.エリアID = tbl_area.エリアID INNER JOIN
                        tbl_shop_machine ON tbl_shop.店舗ID = tbl_shop_machine.店舗ID AND tbl_area.エリアID = tbl_shop_machine.エリアID INNER JOIN
                        tbl1 ON tbl_shop_machine.shop_machineID = tbl1.shop_machineID
GROUP BY      tbl1.使用日, tbl_area.エリア

上記で実行すると、（固定商品数をSUM）

日付　　　　　　　　使用者合計数　　固定商品数計　　エリア

2012/4/26　　　　　11　　　　　　　　22　　　　　　　　　　1

となってしまい、固定商品数計が、重複して計上してしまいます。

固定商品数をGROUP BYで括ると、SUMができず、店舗ごとに表示されます

グループ化したあとの数値を合計する方法はありますでしょうか？

Answer 1

二段階集計ですかね。
以下、空で書いてますので微妙におかしなところがあるかもしれませんが、考え方は汲んでください。

WITH CTE
as
(
SELECT tbl1.使用日, SUM(tbl1.使用者数) AS 使用者合計数, MAX(tbl_shop.固定商品数) AS 固定商品数計, tbl_area.エリア
FROM tbl_shop
INNER JOIN tbl_area ON tbl_shop.エリアID = tbl_area.エリアID
INNER JOIN tbl_shop_machine ON tbl_shop.店舗ID = tbl_shop_machine.店舗ID AND tbl_area.エリアID = tbl_shop_machine.エリアID
INNER JOIN tbl1 ON tbl_shop_machine.shop_machineID = tbl1.shop_machineID
GROUP BY tbl1.使用日, tbl_area.エリア, tbl_shop.shopID
)
select 使用日, SUM(使用者合計数) AS 使用日合計数, SUM(固定商品数計) AS 固定商品数計, エリア from CTE group by 使用日, エリア

考え方はそんなに難しくないです。CTE（共通テーブル式）を使って、使用日、エリア、shopIDでグループ化します。（shopIDという名前は想像で書いています。tbl_shopの主キーです。）
次にグループ化されたレコードセットを、さらに使用日とエリアでグループ化して終わりです。
最初のグループ化で使用者合計数を中間計し、次のグループ化でこの中間計の集計、および固定商品数を集計します。
また、最初のグループ化の時にMAX(tbl_shop.固定商品数)としていることに注意して下さい。グループ化していますので、何らかの集計関数を使わなければならず、とりあえずMAXを使っています。そういう意味なのでMINなどでも良いです。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 2

trapemiya様ありがとうございます。

CTEの存在を知りませんでした。まだまだ、勉強不足で申し訳ありません。

上記問題について、御呈示いただきましたコードで実現することができました。ありがとうございます。

ちなみに、再帰クエリにおいて、除算を行おうとしたところ、int型なので、CASTを使い、上記コードでいうところ

(CAST(SUM(使用者数計)) AS decimal(5, 2)) / (CAST(SUM(固定商品数計)) AS decimal(5, 2)) * 100

で行ったところ式が無効と出ました。

CTEではCASTが使えないのでしょうか？現在調査中です。

Answer 3

CTEを利用して再帰クエリを行うこともできますが、上記の私の例は再帰クエリではないですよ。
CASTはCTEでも普通に使えます。かっこがおかしいようです。

CAST(SUM(使用者数計)) AS decimal(5, 2))

ただしくは、

CAST(SUM(使用者数計) AS decimal(5, 2))

だと思います。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 4

失礼いたしました。私も今気がついて直したところでした。

計算式は問題なかったのですが、型変換で算術オーバーフローエラーが出たので、何がおかしいか見直しています。

Answer 5

decimal（6,2）にすることによって解決しましたが、小数点以下が、13ケタになってしまって、43.2189973614700なんてなってしまいます。本来43.21として表示したいのですが、どうすればいいでしょうか？

Answer 6

計算結果の小数桁数は以下のルールに従っているようです。

有効桁数、小数点以下桁数、および長さ (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms190476.aspx

小数点以下２桁で表示したいのであれば、もう一度castでしょうか？

cast(round(43.2189973614700, 2) as decimal(6,2))

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 7

御返答ありがとうございました。
形としては、

CAST(ROUND(CAST(SUM(maxmen) AS decimal(6 , 2)) / CAST(SUM(maxcount) AS decimal(6 , 2)) * 100,) AS decimal(6 , 2))

という風に行ったのですが、（あっているかどうか・・・）
実際、不適切として実行できませんでした。
もう一度castということを、上記のように理解しているのですが間違っているのでしょうか？

Answer 8

見た目では、100の後にコンマがありますので、これが原因のように思えます。

また、SQL側で無理にフォーマットせず、プログラム側でフォーマットする方法もあります。SQLは生データを返し、それをどう表現するかはアプリケーションに任せてしまうわけです。このようにしておく方が後の仕様変更に強かったり、計算に使う場合はより多くの有効桁数が得られるなどの利点がありますが、必ずこうした方が良いというわけではありません。最終的にはSQLで返される値をどのように使うのか、今後、どのような使い方が予想されるのかで判断することになるでしょう。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

Answer 9

, についてはtrapemiyaさんが既に指摘されているので、

「CAST(SUM(maxmen) AS ～」ということはSUM()による集計をした後にキャストするのですか？ 桁あふれの対処としては逆に思います。

Answer 10

trapemiya様　御返答ありがとうございます。

>100の後にコンマがありますので、これが原因のように思えます。

このコンマについては私もおかしいなと思ったのですが、VS2010上のクエリビルダーで、自動的に配置されるのです。

Answer 11

佐祐理様　御返答ありがとうございます。

>「CAST(SUM(maxmen) AS ～」ということはSUM()による集計をした後にキャストするのですか？

考え方として私が理解していたのが、int型のSUM集計後の除算なので、計算をするに際し、キャストをそれぞれ先にする（しなければ計算結果が’１’となってしまうから）と思っています。

>桁あふれの対処としては逆に思います。

上記考え方から、どういうことを言われているかわからないです。

Answer 12

皆様ありがとうございます。

以下で解決いたしました。

CAST(CAST(SUM(maxmen) AS decimal(6 , 2)) / CAST(SUM(maxcount) AS decimal(6 , 2)) AS decimal(6 , 2))

実行結果も問題ありません。ただ、これでよいのかの疑問はあります。佐祐理様の言われていたことです。