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

    質問

  • 環境 VS2010 SQLServer2008 VB 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ができず、店舗ごとに表示されます

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






    2012年4月26日 4:39

回答

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

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

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

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


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

    • 回答としてマーク kazukazu2002 2012年5月1日 4:36
    2012年4月27日 5:02
  • 皆様ありがとうございます。

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

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

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

    • 回答としてマーク kazukazu2002 2012年5月1日 4:35
    2012年5月1日 2:53

すべての返信

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

    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/




    2012年4月26日 6:11
  • trapemiya様ありがとうございます。

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

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

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

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

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

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

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

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

    ただしくは、

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

    だと思います。


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

    2012年4月26日 7:38
  • 失礼いたしました。私も今気がついて直したところでした。

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


    2012年4月26日 7:47
  • decimal(6,2)にすることによって解決しましたが、小数点以下が、13ケタになってしまって、43.2189973614700なんてなってしまいます。本来43.21として表示したいのですが、どうすればいいでしょうか?
    2012年4月27日 2:37
  • 計算結果の小数桁数は以下のルールに従っているようです。

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

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

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


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

    • 回答としてマーク kazukazu2002 2012年5月1日 4:36
    2012年4月27日 5:02
  • 御返答ありがとうございました。
    形としては、

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

    という風に行ったのですが、(あっているかどうか・・・)
    実際、不適切として実行できませんでした。
    もう一度castということを、上記のように理解しているのですが間違っているのでしょうか?
    2012年5月1日 1:23
  • 見た目では、100の後にコンマがありますので、これが原因のように思えます。

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


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

    2012年5月1日 1:47
  • , についてはtrapemiyaさんが既に指摘されているので、

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

    2012年5月1日 1:50
  • trapemiya様 御返答ありがとうございます。

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

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

    2012年5月1日 2:20
  • 佐祐理様 御返答ありがとうございます。

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

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

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

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



    2012年5月1日 2:23
  • 皆様ありがとうございます。

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

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

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

    • 回答としてマーク kazukazu2002 2012年5月1日 4:35
    2012年5月1日 2:53