- ベストアンサー
成績と性別毎の人数分布を集計するSQL文は?
SQL文で成績と性別での人数分布を求めるクロス集計関数が出来ず困ってます。 サンプルとしては 学年 性別 評価(ABC) 1年 男 A 1年 女 A 1年 男 A 1年 男 B 2年 男 B 2年 女 C 欲しい出力としては各サンプル毎の人数です。 1年 男-A評価,男B評価,男C評価,女A評価,女B評価,女C評価 2年 男-A評価,男B評価,男C評価,女A評価,女B評価,女C評価 3年 男-A評価,男B評価,男C評価,女A評価,女B評価,女C評価 ・ ・ ・ という具合です。 以前は SELECT COUNT(*) AS 分布人数, 性別, 成績 FROM 成績 GROUP BY 年齢, 性別, 学年 HAVING 学年 = @がくねん ORDER BY 年齢,成績 というSQL文で1学年毎に抽出していました。 クロス集計で1つのSQL文で出力したいのですがいかがでしょうか?
- oo5goo
- お礼率38% (5/13)
- SQL Server
- 回答数2
- ありがとう数6
- みんなの回答 (2)
- 専門家の回答
質問者が選んだベストアンサー
表の列構成、元のデータ例、得たい結果例が示されていると、回答する側としても分かりやすいのですけどね。 次のようなSQLでは、いかがでしょうか? select 学年, count(case when 性別='m' and 評価='A' then 1 else null end) as 男A, count(case when 性別='m' and 評価='B' then 1 else null end) as 男B, count(case when 性別='m' and 評価='C' then 1 else null end) as 男C, count(case when 性別='w' and 評価='A' then 1 else null end) as 女A, count(case when 性別='w' and 評価='B' then 1 else null end) as 女B, count(case when 性別='w' and 評価='C' then 1 else null end) as 女C from t1 group by 学年 ;
その他の回答 (1)
- t-ka
- ベストアンサー率28% (14/50)
んーこんなのはどうでしょう、長いですけど構造は単純です。 良ければ質問者さま用に適宜書き換えてみてください。 SELECT '第一学年' AS 学年, COUNT(CASE WHEN nenndo = '1' and sex = '男' and hyouka = 'A' THEN 1 ELSE null END) AS men_A, COUNT(CASE WHEN nenndo = '1' and sex = '男' and hyouka = 'B' THEN 1 ELSE NULL END) AS men_B, COUNT(CASE WHEN nenndo = '1' and sex = '男' and hyouka = 'C' THEN 1 ELSE NULL END) AS men_C, COUNT(CASE WHEN nenndo = '1' and sex = '女' and hyouka = 'A' THEN 1 ELSE NULL END) AS women_A, COUNT(CASE WHEN nenndo = '1' and sex = '女' and hyouka = 'B' THEN 1 ELSE NULL END) AS women_B, COUNT(CASE WHEN nenndo = '1' and sex = '女' and hyouka = 'C' THEN 1 ELSE NULL END) AS women_C FROM test20070707 UNION SELECT '第二学年' AS 学年, COUNT(CASE WHEN nenndo = '2' and sex = '男' and hyouka = 'A' THEN 1 ELSE NULL END) AS men_A, COUNT(CASE WHEN nenndo = '2' and sex = '男' and hyouka = 'B' THEN 1 ELSE NULL END) AS men_B, COUNT(CASE WHEN nenndo = '2' and sex = '男' and hyouka = 'C' THEN 1 ELSE NULL END) AS men_C, COUNT(CASE WHEN nenndo = '2' and sex = '女' and hyouka = 'A' THEN 1 ELSE NULL END) AS women_A, COUNT(CASE WHEN nenndo = '2' and sex = '女' and hyouka = 'B' THEN 1 ELSE NULL END) AS women_B, COUNT(CASE WHEN nenndo = '2' and sex = '女' and hyouka = 'C' THEN 1 ELSE NULL END) AS women_C FROM test20070707 UNION SELECT '第三学年' AS 学年, COUNT(CASE WHEN nenndo = '3' and sex = '男' and hyouka = 'A' THEN 1 ELSE NULL END) AS men_A, COUNT(CASE WHEN nenndo = '3' and sex = '男' and hyouka = 'B' THEN 1 ELSE NULL END) AS men_B, COUNT(CASE WHEN nenndo = '3' and sex = '男' and hyouka = 'C' THEN 1 ELSE NULL END) AS men_C, COUNT(CASE WHEN nenndo = '3' and sex = '女' and hyouka = 'A' THEN 1 ELSE NULL END) AS women_A, COUNT(CASE WHEN nenndo = '3' and sex = '女' and hyouka = 'B' THEN 1 ELSE NULL END) AS women_B, COUNT(CASE WHEN nenndo = '3' and sex = '女' and hyouka = 'C' THEN 1 ELSE NULL END) AS women_C FROM test20070707
お礼
t-kaさんありがとうございました。 参考にさせて頂きます。
関連するQ&A
- 集計を求めるSQL文について
こんにちは。 はじめまして、MMM-SRVと申します。 初めて投稿させて頂きます。 宜しくお願い致します。 MS Access2000をデータベースとしてASPでプログラムを組んでおります。 その中でどうしてもわからないので教えて下さい。 create table t1 ( age varchar(30), -- 年代 sex varchar(30), -- 性別(男'0'女'1') txt varchar(30) -- 氏名 ); というテーブルに age | sex | txt ----+-----+------ 1 | 0 | A 2 | 0 | B 2 | 1 | C 1 | 1 | D 2 | 0 | E というデータがあるとして、ここから age | 男 | 女 ----+----+---- 1 | 1 | 1 2 | 2 | 1 のように集計するSQL文をご教授頂けませんでしょうか。 postgrsであれば、以下のように組んでみるのですが case演算子がMS Accessでは使えないようなので…。 select age,sum(case when sex=0 then 1 else 0 end) as 男,sum(case when sex=1 then 1 else 0 end) as 女 from t1 group by age; 宜しくお願い致します。
- ベストアンサー
- Microsoft ASP
- 成績の合計を集計するSQLを考えています。
成績の合計を集計するSQLを考えています。 下記のようなテーブルをAccessで作成しました。 ID 科目 学期 中間 期末 学年 性別 0001 001 01 30 32 1 男子 0001 002 01 20 31 1 男子 0001 003 01 22 23 1 男子 0001 001 02 30 11 1 男子 0001 002 02 23 33 1 男子 0001 003 02 22 23 1 男子 ID 生徒を識別 科目 001=国語 002=数学 003=理科 学期 01=前期 02=後期 中間 科目のテスト結果を入力 期末 科目のテスト結果を入力 ※学期ごとに中間と期末があります。 下記のように学期別に同学年かつ同性の科目ごとの合計を集計する SQLを考えています。 期 性別 国語 理科 社会 前期 男子 200 250 300 前期 女子 110 200 340 後期 男子 150 230 360 後期 女子 170 250 350 学期別に集計をすることは出来たですが、さらに性別で分けることができませんでした。 アドバイスお願いします。 また、現在は SELECT [学年], iif( [学期]=1,"前期","後期") AS [学 期], Sum(IIf([科目]=1,[中間]+[期末],0)) AS [国 語], Sum(IIf([科目]=2,[中間]+[期末],0)) AS [数 学], Sum(IIf([科目]=3,[中間]+[期末],0)) AS [理 科] FROM テーブル名 GROUP BY [学年], [学期]; のようなSQL文を作成しました。
- 締切済み
- その他(プログラミング・開発)
- SQL文でクロス集計をしたい
アクセスでクロス集計のSQL文で作りたいと思っています。 このようなデータがあります 年月 支払先 数量 金額 201301 A 10 1000 201302 A 5 500 201302 B 20 2000 201301 C 10 3000 201303 C 15 1500 このデータを下記のように表示したいのですが、 分からなくて困っています。 支払先 データ 201301 201302 201303 A 数量 10 5 A 金額 1000 500 B 数量 20 B 金額 2000 C 数量 10 15 C 金額 3000 1500 合計 数量 20 25 15 合計 金額 4000 2500 1500 どのようにすれば実現出来ますか? 今は、 SELECT 支払先, "数量" AS データ, Sum(IIf([年月]='201301',[数量],0)) AS 201301, Sum(IIf([年月]='201302',[数量],0)) AS 201302, Sum(IIf([年月]='201303',[数量],0)) AS 201303, FROM T_データ GROUP BY 支払先; のクロス集計を 数量と金額、数量の合計と金額の合計でクロス集計をしたクエリを UNIONで実現しています。 出来れば数量と金額は1つのクエリで実現したいと思いますが方法があれば教えて頂きたい と思います。 どうぞ、よろしくお願い致します。
- ベストアンサー
- その他(データベース)
- 集計のSQLについて
皆さんの知恵をお借りしたいと思っています。 どなたか、良い案がある方お願いいたします。 以下のテーブルがあります。 (売上テーブル) 品名 金額 購入者 ---------------------------- 鉛筆 20 Aさん 鉛筆 40 Bさん 鉛筆 30 Bさん 鉛筆 30 鉛筆 10 上のテーブルで、品名・購入者を集計キーとして、 金額を集計したい場合、 SELECT 品名,購入者,SUM(金額) FROM 売上テーブル GROUP BY 品名,購入者 というSQL文で, 品名 金額 購入者 ---------------------------- 鉛筆 20 Aさん 鉛筆 70 Bさん 鉛筆 40 という結果が得られると思いますが・・・、 これを、 品名 金額 購入者 ---------------------------- 鉛筆 20 Aさん 鉛筆 70 Bさん 鉛筆 30 鉛筆 10 という結果になるようなSQL文を作りたいのです。 購入者が入っているものについては、品名・購入者で集計して、 購入者が空欄のものは集計しないようにしたいと思っています。 UNIONを使えばできるとは思うのですが、 それ以外の方法で、1回のSQL文でできる方法があれば知りたいと思っています。 よろしくお願いします。
- ベストアンサー
- Oracle
- SQL文
こんにちは。 ACCESS2000を使用しています。 テーブルに受付番号と性別(男性=1・女性=2)のフィールドがあります。 性別のフィールドだけを使用して、男性人数・女性人数・男女の合計人数・男性比率・女性比率を求めるSQL文の書き方がわかりません。 アドバイスよろしくお願いいたします。 ※下記SQLで性別毎の人数までは出せるのですが。。。 SELECT 性別, Count(性別) AS 人数 FROM データテーブル GROUP BY 性別;
- ベストアンサー
- その他(データベース)
- こんなSQL文
Oracle8.1.7を使用しております。 以下のようなテーブルに対し、1行目と2行目を削除するSQL文を書きたいのですが、思いつきません。どなたか、アドバイスいただけないでしょうか? 表名:A B | C ―――――――― 2 | 10 2 | 1 1 | 10 1 | 1 1 | 11 1 | 2 1 | 1 3 | 10 2 | 3 変数として、P1=1とP2=2があります。(これは、SQL文の中に直接書くものとします。) ここで、SELECT B,C FROM A WHERE B=1;とSELECT B,C FROM A WHERE B=2;を実行すると、次のようになります。 B | C B | C ――――――――― ――――――――― 1 | 10 2 | 10 1 | 1 2 | 1 1 | 11 2 | 3 1 | 2 1 | 1 ここで、それぞれの表を見ると、Cの値が一致している部分があります。この一致していて、Bが2の行を削除したいのですが、SQL1文では無理でしょうか? 最近、SQLには慣れてきたつもりだったのですが、こんなとこでつまずくとは・・・どうぞよろしくお願いします。
- ベストアンサー
- その他(データベース)
- SQL文がわかりません。
SQL文がわからなく困っております。 下記のデータ*01を*02のデータのようにSELECTするためには、 どのようなSQL文を書けばよいのかお教えいただけますでしょうか? DBはoracle11gです。 ※01 ID 所属部署 _____________________________ 1 a 1 b 2 a 2 b 2 c 3 a 3 b 3 c 3 d ____________________________ ↓↓↓↓↓↓↓↓↓↓↓↓↓↓ ※02 ID 所属部署 所属部署2 所属部署3 所属部署4 _________________________________________ 1 a b 2 a b c 3 a b c d
- ベストアンサー
- Oracle
- SQL文
名前、性別、年齢の載っているA表から19歳以下の女性、または26歳以上の女性の名前を抽出するSQL文はSELECT 名前 FROM 表A WHERE 性別=’女’AND 年齢<20 OR 性別=’女’AND 年齢>25 が正解ですが、性別=’女’を1回にして SELECT 名前 FROM 表A WHERE 性別=’女’AND 年齢<20 OR 年齢>25 ではだめでしょうか。教えてください。
- ベストアンサー
- その他(データベース)
- 分位点と正規分布で成績を測る
学校でのアンケート調査で自分のテスト順位を10位単位で答えてもらいました(例:10~20位 や50~60位など) 各学年で人数が違うので、75人いる学年は70~80位までの回答になり、100人いる学年は100~110位のまでの回答になりました。 全員が正確に回答したら、例えば75人いる学年ならば、 1~10位が約10人、 10~20位も約10人、 20~30位も約10人・・・・ となるはずですが、実際はバラバラな人数でした。 これら各学年の人数が違いますが、学年を超えて一括してその学力を測るには、偏差値が必要になります。 普通なら、100点満点のテストで偏差値をとりますが、 この場合成績を順位で聞いていますので、分位点で偏差値をわりふる必要があるそうです。 そして、このばあい正規分布の分布表を使う必要があるそうです。 分位点で偏差値を割り振る方法を教えてください。 そして、この場合の正規分布の分布表はどのようにだすのですか?? その分布表をどのように使うのでしょうか。 わかりにくかったらごめんなさい。 よろしくお願いします。
- ベストアンサー
- 数学・算数
お礼
chukenkenkouさんありがとうございました。 ほぼ、このSQL文で欲しい出力が出ました。