• ベストアンサー
  • すぐに回答を!

複数テーブルの集計

お世話になっています。 複数テーブルの集計がわからないので質問させてください。 テーブル3のItem1~4にはテーブル2のItemIDを登録します。 エリア毎の参加人数をcsvデータを出力させたいです。 テーブル3においてテーブル2のItemIDが4つあるので、一人に対して4行必要なのかと思いましたが、テーブルの変更ができません。 テーブルの結合まではできましたがその後ができません。 テーブルの変更ができませんので、結合するためにテーブル2の構造とデータが同じテーブルを他に3つ作成しています。 どうぞよろしくお願いします。 テーブル1 AreaID   AreaName --------------------- 1 北海道 2 東北 3 関東 4 北陸 テーブル2 ItemID ItemName --------------------- 1 自由形50 2 自由形100 3 背泳ぎ50 4 平泳ぎ50 テーブル3 ID AreaID   Name  Item1   Item2  Item3 Item4 --------------------------------------------------------- 1   2    鈴木    2    1    4     3 2   3    佐藤    1    null   3    null 3   1    田中    1    2    null   null 4   2    伊藤    3    null   2    4  5   3    川村    null   2    3    null 「集計結果」       参加人数 自由形50 自由形100 背泳ぎ50 平泳ぎ50 --------------------------------------------------------------- 北海道    1     1     1     0     0  東北     2     1     2     2     2   関東     2     1     1     2     0   北陸     0     0     0     0     0

共感・応援の気持ちを伝えよう!

  • MySQL
  • 回答数4
  • ありがとう数4

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.1

MySQLの質問をする場合は、バージョンを明記しましょう。せっかく具体的なSQLを提示しても、質問者さんの環境では実行できない可能性があります。 >テーブルの変更ができません どう頑張っても性能を出せなかったり、今後の機能拡張などが困難になったりしますよ? 下記SQLは、MySQL 4.1以降なら動くと思います。 テーブル2は、未使用です。 【SQL例】 select AreaName, count(distinct ID) 参加人数, count(case when Item=1 then 1 else null end) as 自由形50, count(case when Item=2 then 1 else null end) as 自由形100, count(case when Item=3 then 1 else null end) as 背泳ぎ50, count(case when Item=4 then 1 else null end) as 平泳ぎ50 from t1 as x left join ( select ID,AreaID,1 as Item from t3 where Item1=1 or Item2=1 or Item3=1 or Item4=1 union all select ID,AreaID,2 as Item from t3 where Item1=2 or Item2=2 or Item3=2 or Item4=2 union all select ID,AreaID,3 as Item from t3 where Item1=3 or Item2=3 or Item3=3 or Item4=3 union all select ID,AreaID,4 as Item from t3 where Item1=4 or Item2=4 or Item3=4 or Item4=4 ) as y on x.AreaID=y.AreaID group by AreaName order by x.AreaID ;

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ご回答ありがとうございました。 教えていただいたSQLで実現できました! テーブル2は使うものとばかり考えていました… バージョンはMySQL4.1.18です。提示せずすみません。。 テーブルの変更は改めてお願いしてみます。 本当にありがとうございました。

質問者からの補足

すみません。もうひとつだけ教えていただきたいことがあります。 PHPで開発しており、教えていただいたSQLを $sql="select AreaName, count(distinct ID) 参加人数, count(case when Item=1 then 1 else null end) as 自由形50, count(case when Item=2 then 1 else null end) as 自由形100, count(case when Item=3 then 1 else null end) as 背泳ぎ50, count(case when Item=4 then 1 else null end) as 平泳ぎ50 from t1 as x left join ( select ID,AreaID,1 as Item from t3 where Item1=1 or Item2=1 or Item3=1 or Item4=1 union all select ID,AreaID,2 as Item from t3 where Item1=2 or Item2=2 or Item3=2 or Item4=2 union all select ID,AreaID,3 as Item from t3 where Item1=3 or Item2=3 or Item3=3 or Item4=3 union all select ID,AreaID,4 as Item from t3 where Item1=4 or Item2=4 or Item3=4 or Item4=4 ) as y on x.AreaID=y.AreaID group by AreaName order by x.AreaID"; としました。 しかし、PHPMyAdmin上からSQL文は問題なく通り期待した結果が得られましたが、 PHPからは Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource というエラーがでます。 mysql_error()で確認したところ以下のようなエラーがでました。 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '参加人数, count(case when Item=1 then 1 else null end) as 自由形 参加人数を「''」で囲むなどしてみましたが見当違いでした… お手数かけて申し訳ないのですが、ご教授ください。

その他の回答 (3)

  • 回答No.4

#1回答者です。 確かに「as」を忘れていましたね。 ただ、「式 as 別名」の「as」は、多くのRDBMSで省略可能であり、MySQLでも同様に省略可能です。 →マニュアルのselect構文のところにも、明記されています。 ところで、「回答への補足」と「回答へのお礼」は、どちらが最新のステータスなのかが分からない(ここのシステムの仕組みが分かっていない)のですが、まだ、PHPでは実行できていないのでしょうか?

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ご回答ありがとうございました。 MyAdminから試して問題がなかったのでお礼を投稿させていただき、 PHPからエラーがでたので補足を追加させていただきました(^^; しかし、先ほどエラーが解決致しました! 参加人数などの列名を英語にしたところ大丈夫でした。 日本語がエラーになったのは、余計な文字などがあったかもしれないです。この原因は調べている最中です。 おかげさまで実現できました!本当にありがとうございました。

  • 回答No.3
  • hrm_mmm
  • ベストアンサー率63% (292/459)

エラー表示のところの >syntax to use near '参加人数 '参加人数' の前に as が必要だと思いますけど、PHPMyAdminでエラーが出ないのもちょっと解りかねます。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ご回答いただきありがとうございます。 asがないことに気づきPHPからはつけさせていただきました。 MyAdminからはasがなくても大丈夫でした… asをつけてもエラーがでたままです。

  • 回答No.2
  • hrm_mmm
  • ベストアンサー率63% (292/459)

まず、table1とtable3から集計出来るとして、項目名は直書きするしかないかな MySQL4.0.1では以下のように select table1.AreaName as area,count(table3.ID) as '参加人数', count(table3.Item1) as '自由形50',count(table3.Item2) as '自由形100', count(table3.Item3) as '背泳ぎ50', count(table3.Item4) as '平泳ぎ50' from table1 left join table3 on table1.AreaID = table3.AreaID group by table1.AreaName; または、phpとか他のプログラム言語との連携で項目名変換表示することになるのでは?

共感・感謝の気持ちを伝えよう!

関連するQ&A

  • 複数テーブルの集計その2

    お世話になります。 先日テーブルの集計について教えていただき、下記集計結果を取得することができたのですが、 新たに自由形50~平泳ぎ50までの合計を追加したいのです。(一人で4種目参加する場合は4と数える) テーブル1 AreaID   AreaName --------------------- 1 北海道 2 東北 テーブル3 ID AreaID   Name  Item1   Item2  Item3 Item4 --------------------------------------------------------- 1   2    鈴木    2    1    4     3 2   1    田中    1    2    null   null 3   2    伊藤    3    null   2    4  「集計結果」       参加人数 自由形50 自由形100 背泳ぎ50 平泳ぎ50  追加部分 --------------------------------------------------------------- 北海道     1     1       1      0      0       2 東北      2     1       2      2      2       7   $sql="select AreaName, count(distinct ID) 参加人数, count(case when Item=1 then 1 else null end) as 自由形50, ・・・略 from t1 as x left join (select ID,AreaID,1 as Item from t3 where Item1=1 or Item2=1 or Item3=1 or Item4=1 union all select ID,AreaID,2 as Item from t3 where Item1=2 or Item2=2 or Item3=2 or Item4=2 ) as y on x.AreaID=y.AreaID group by AreaName order by x.AreaID というようにおしえていただきました。 null以外を取得するSQLをカウントとselectに追加すればいいのかと思い試したのですが期待した値を取得できません。 count(case when Item=AAA then 1 else null end) as abc,と union all select ID,AreaID,AAA as Item from テーブル3 where ItemID1<>'' or ItemID2<>'' or ItemID3<>'' or ItemID4<>'' or (ItemID1<>'' and ItemID2<>'') ・・・ バージョンはMySQL4.1.18です。 よろしくお願い致します。

    • ベストアンサー
    • MySQL
  • アクセスの集計方法?正規化?について

    以前も、少し質問したことがあったのですが、集計クエリについての質問です。今AのテーブルとBのテーブルをクエリで結合していて、簡単に書くと下記のようなてーぶるになっています。 【県名】  【地方】  【数量】  群馬    関東     10  埼玉    関東     15  栃木    関東     20  東京    関東     50  新潟    北陸     15  富山    北陸     15  石川    北陸     35  鳥取    中国      8  島根    中国     12  広島    中国     40 こういったクエリで作ったテーブルの地方別に合計値を出したいのですがどうすればいいのでしょうか? それから、正規化をよく知らないのですが、欲をいえば、正規化のように地方をレコード一つにして、県名を別のフィールドに並べるとかってできませんかね? アクセス超初心者のため意味不明な質問になりましたが、よろしくお願いします。

  • 高速道路で仮眠できる施設のあるところ

    北陸、東北、関東、北海道で仮眠のできるところがありましたら教えてください。また、料金はいくらくらいかかりますでしょうか?

  • アクセスの正規化?

    前に質問したことがあったのですが、やってみてできなかったので再度質問させてもらいます。今AのテーブルとBのテーブルをクエリで結合していて、簡単に書くと下記のようなテーブルになっています。 【県名】  【地方】  【数量】  群馬    関東     10  埼玉    関東     15  栃木    関東     20  東京    関東     50  新潟    北陸     15  富山    北陸     15  石川    北陸     35  鳥取    中国      8  島根    中国     12  広島    中国     40 これを、下記の様に変えたいんですが、どうすればいいでしょうか? <第1段階> 【地方】  【県名】  【数量】  関東    群馬     90  関東    埼玉     90  関東    栃木     90  関東    東京     90  北陸    新潟     65  北陸    富山     65  北陸    石川     65  中国    鳥取     60  中国    島根     60  中国    広島     60 <第2段階> 【地方】   【県名】        【数量】  関東  群馬・埼玉・栃木・東京     90  北陸   新潟・富山・石川      65  中国   鳥取・島根・広島      60   このように最終的には第2段階にしたいのですが、やり方がわかりません。どなたか、分かる方教えて下さい。ちなみに集計クリエの「グループ化」「合計」では、できませんでした。 お願いします。

  • MySQLでJOINを使った検索について

    MySQLについて質問があります。 下記のような2テーブルがあります。 ----------------------------- ・item 商品情報を格納。 ・usersitem ユーザーが所有している商品の個数を格納。 ----------------------------- この2つのテーブルから2つのリストを取り出したいと考えています。 【A】特定のユーザーが複数所有している商品の一覧 【B】特定のユーザーが所有していない商品の一覧 【A】は出来たのですが、【B】のSQL文がわかりません。 どうかご教授いただけませんでしょうか。 ■テーブルを作成したSQL ----------------------------- CREATE TABLE `test`.`item` ( `itemid` SERIAL NOT NULL DEFAULT NULL UNIQUE, `itemname` VARCHAR( 256 ) ); CREATE TABLE `test`.`usersitem` ( `id` SERIAL NOT NULL DEFAULT NULL UNIQUE, `userid` INT, `itemid` INT, `count` INT ); ----------------------------- ■【A】を実現したSQL 条件:userid「1」のユーザーがcount「2」以上の一覧。 ----------------------------- SELECT * FROM `item` LEFT JOIN `usersitem` ON (`item`.`itemid` = `usersitem`.`itemid`) WHERE `usersitem`.`userid` = 1 AND `usersitem`.`count` >= 2 ----------------------------- ■【B】を実現しようとしたが違っていたSQL 条件:userid「1」のユーザーがcount「0」以下、または登録されていない一覧。 ----------------------------- SELECT * FROM `item` LEFT JOIN `usersitem` ON (`item`.`itemid` = `usersitem`.`itemid`) WHERE ( `usersitem`.`userid` = 1 AND `usersitem`.`count` <= 0 ) OR `usersitem`.`userid` != 1 ----------------------------- 結果: 個数情報が登録されていない商品が表示されない。 違うユーザーの情報が表示されてしまう。 使用しているのは MySQL 5.5.29です。 よろしくお願いいたします。

  • sqlservrのシステムテーブルから主キー判定

    sqlservrのシステムテーブルのから主キーの判定を行いたいです。 sysobjects(type='U')でテーブル情報があり、それに紐付くsyscolumnsの項目情報があるとします。 テーブル名,項目名 tbl1,item1 tbl1,item2 tbl1,item3 tbl2,item1 tbl2,item2 これは、 select obj.name as "テーブル名" , col.name as "項目名" from sysobjects obj left join syscolumns col on obj.id = col.id where obj.type = 'U' で取得できますが、 さらに、主キーの項目には○をつけたいです。 テーブル名,項目名,主キー tbl1,item1,○ tbl1,item2,○ tbl1,item3,null tbl2,item1,○ tbl2,item2,null sysindexkeysやsysindexesの情報を引っ掛ければなんとかなるかと思いましたが、何ともならずにGive UP状態です。 システムテーブルを結合して取得する方法はありますでしょうか?

  • 日本の地方

    日本の地方は近畿、北陸、中部、東海、九州、北海道、沖縄、中国、四国、関東、東北、11地方ですか?。

  • エクセルのピボットテーブルについて

    エクセルのピボットテーブルを使い始めたのですが、本等を読んでもよく分かりません。知りたいのは (1)クロス集計というが、原則あるタイトルのある表の2つの項目(フィールド)についてのみクロス集計が出来るのか?3つ以上のフィールドをクロスさせることで、データの個数等を調べたいのです。具体的には、ある会社組織の社員のの「職番(数値)」「氏名」「所属部署」「職位(事務か技術とか)」「階級」「性別」「給与」「年齢」「社会保険番号」等のタイトルのあるエクセルのデータシートで(それぞれのタイトルの列をフィールドとする、行はレコードの集まり)「所属部署」ごとに「職位」や「階級」の人数を出そうとします。2003ですが行の所に所属部署、列のドラッグ位置に職位でデータアイテムのドラッグ位置には氏名を入れればとりあえずそれぞれの人数は出ます。が、ここで更に性別も区別して人数として把握出来る表にするにはどのようにしたらよいのでしょうか? (2)又、この場合にもあてはまりますが、1つのレコードを特定できるものとして職番がありますがこれが単なる数値であればデータアイテムのドラッグ位置にこの項目をドラッグしても人数の集計には使えないでしょうか?文字列に変えておく必要がありますか?数値のままうまく数えられる方法があったら教えて下さい。

  • セルをツリー式にたたむ方法

    A列に大分類名を入れ、B列に小項目を入れたシートで、必要のないB列の項目をたたんでおく事は出来るでしょうか。 例えば・・。  A列  B列 北海道 渡島 東北  青森 関東  東京 北陸  新潟  ・  ・  ・  ・ と閉じていて、クリックなどで例えば東北だけB列を開く  A列  B列 北海道 渡島 東北  青森      秋田      岩手      宮城      福島      山形 関東  東京 北陸  新潟  ・  ・ という感じです。 以前なにかで見た気がするんですが。

  • 全国の大きい美術館

    北海道・東北・関東・甲信越・北陸・関西・中国四国・九州の8つの地域で一番大きい美術館はどこでしょうか?卒業論文の制作も兼ねて、巡りたいと思っています! 各地域の方々よろしくお願いいたします!