サインイン
 
ホーム20122008 R22008200520007.0ライブラリフォーラム
SQL Server >
> 
> 
重複値の加算

回答済み 重複値の加算

  • 2012年5月7日 7:14
     
     
    サインインして投票
    0

    お世話になっております。

    SQLについてご質問があります。お力添え頂ければと思います。

    下記のような表があります。

    SELECT 勉強会コード, 参加クラス, 参加生徒, 出席 FROM TABLE1

    勉強会コード 参加クラス 参加生徒 出席
    001 クラスA Aさん 1
    001 クラスA Bさん 1
    002 クラスA Aさん
    		 1
    003 クラスA Aさん 1
    001 クラスB Cさん 0
    001 クラスB Dさん 0

    ...以下勉強会コードや参加クラス等が続いていきます。

    上記を、下記のような集計を行うクエリが導き出せず困っております。

    参加クラス 参加数 勉強会数 出席率
    クラスA 3 3 100%
    クラスB 2 4 50%
    クラスC 1 3 33%
    クラスD 2 5 40%




    各クラスの勉強会に対する出席率を求めるというのが趣旨ですが、出席率自体はVB側でDataTableや変数を使い、導く予定です。
    一回の勉強会に対し、一クラスから複数の参加者が居た場合は、参加数＝「１」としてカウントする必要があります。
    ※勉強会001に対し、クラスAからAさん、Bさんが参加したとしても、クラスAの勉強会001に対する参加数は「１」です。

    以上、ご教示頂きたく思います。宜しくお願いします。

     

すべての返信

  • 2012年5月7日 8:01
     
      コードあり
    サインインして投票
    1

    未確認な上、力技感が半端無いですが・・・

    SELECT 参加クラス
         , SUM(出席) AS 参加数
         , SUM(勉強会数) AS 勉強会数
         , SUM(出席) / SUM(勉強会数) AS 出席率
      FROM
         (
           SELECT 参加クラス
                , 勉強会コード
                , MAX(出席) AS 出席
                , COUNT(DISTINCT 勉強会コード) AS 勉強会数
             FROM TABLE1
            GROUP BY
                  参加クラス
                , 勉強会コード
         ) AS WK
     GROUP BY
           参加クラス

    こんな感じでどうですかね。

    ※「出席」列が数値型の前提で書いてあるので、数値でない場合は調整して下さい。( MAX(出席) => CONVERT(NUMERIC(1), MAX(出席)) )

    ただ、別の話として環境（SQL Serverのバージョン）は書きましょう。


    • 編集済み aviator__ 2012年5月7日 9:02
    •  
     
  • 2012年5月7日 8:23
     
     回答済み コードあり
    サインインして投票
    1

    例示されている範囲だと、参加数と勉強会数が同じ３になってしまっているので違いが判りませんでした。参加数の方はWHERE 出席=1の条件が付き、勉強会数の方はその条件が付かないということでしょうか？

    WITH 参加 AS (
  SELECT 参加クラス, COUNT(DISTINCT 勉強会コード) AS 数
  FROM TABLE1
  WHERE 出席=1
      GROUP BY 参加クラス
), 勉強会 AS (
  SELECT 参加クラス, COUNT(DISTINCT 勉強会コード) AS 数
  FROM TABLE1
      GROUP BY 参加クラス
    )
    SELECT 勉強会.参加クラス, 参加.数 AS 参加数, 勉強会.数 AS 勉強会数, ISNULL(参加.数, 0) / 勉強会.数 AS 出席率
    FROM 勉強会
    LEFT OUTER JOIN 参加 ON 勉強会.参加クラス=参加.参加クラス;
    とか適当に。

     
  • 2012年5月7日 9:27
     
     回答済み コードあり
    サインインして投票
    1

    SQLの実行プランを確認しましたが、佐祐理さんのSQLの方が1段階シンプルに解析されますね。

    個人的に再帰クエリ以外でCTEを使う事に抵抗があるのですが、
    ※初めて知った時に多用した所、後でSQLを見た後輩に順番に質問されて面倒だったという後ろ向きな理由デス・・・
    大きなお世話とは思いつつ少し手直しさせて下さい。

    微妙な違いですが

    SELECT 勉強会.参加クラス, 参加.数 AS 参加数, 勉強会.数 AS 勉強会数, ISNULL(参加.数, 0) / 勉強会.数 AS 出席率

    の所は、

    SELECT 勉強会.参加クラス, ISNULL(参加.数, 0) AS 参加数, 勉強会.数 AS 勉強会数, ISNULL(参加.数, 0) / CONVERT(NUMERIC, 勉強会.数) AS 出席率

    の方が良いかもしれないです。

    改めてCTE使いたくなった今日この頃・・・

     
  • 2012年6月14日 6:49
     
     
    サインインして投票
    0

    佐祐理　様

    aviator__　様

    お礼が遅くなり申し訳ありません。

    返信を頂きましてありがとうございます。

    あれ以降、別途Viewを用い、そこから希望の値を引き出す方法を選ぶことになってしまったのですが、
    お二人の提示していただいた方法も試させて頂きました。
    本来はもっと別の条件も絡んでいたため、自分の中でお二人の方法を上手くものに出来ず、応用出来ずじまいになってしまったため、今後ももっと勉強していく所存です。

    この度はお二人とも貴重な時間を割いて頂きまして、ありがとうございました。