質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.64%

SQL文でGROUP BYによる件数合計の合成出力の方法

解決済

回答 6

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 31K+

seastar3

score 1619

SQLのGroup by を使った件数カウントで行き詰まっていますので、ぜひお教え下さい。

部登録テーブル
生徒コード 性別コード 部コード
1101     1          5
1102     2          3
:      :          :

普通に男子の部ごとの人数であれば、
SELECT 部コード,COUNT(生徒コード) FROM 部登録テーブル WHERE 性別コード = 1 GROUP BY 部コード;
女子の部ごとの人数であれば、
SELECT 部コード,COUNT(生徒コード) FROM 部登録テーブル WHERE 性別コード = 2 GROUP BY 部コード;
のコードで以下のように出力できます。

部コード COUNT(生徒コード)
1             10
3              2
8              6
:              :

実際これでPHPで実装し、MySQLから取り出しているのですが、全体のビューと男子のビューと女子のビューと切り替えて出力するだけではなく、次のように男女計を並べて出力するにはどう記述したらよいのでしょうか

部コード 男子計 女子計 合 計
1         10        0       10
2          0       15       15
3          2         6         8
:           :         :          :

JOIN を使うのでしょうが、何回か挑戦しては挫折しています。よろしくお願いします。


皆様のおかげで無事実装できました。以下のように出るようになりました。

このやり方が10年以上分からなかったので,感無量です。
このテクニックを応用して、欠課レコードの科目別合計や得点レコードの科目別合計などに取り組んでみます。
回答していただいた皆様どうもありがとうございました。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 6

+3

条件付き集計ですね。
割とよくやる機会は出てくるので割と情報も多かったりします。

よくあるパターンでは、
CASE式とSUM関数を組み合わせて実現します。
こちらの「異なる条件の集計を1つのSQLで行う」という項目の説明が、
正にやりたいことに該当するのではないでしょうか。

掲示したサイトは、
CASE式以外にも役立つノウハウが説明されており、
一読する価値があるので興味があれば他の項目も参照してみてください。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/11/01 01:38

    Panzer_vorさん、早速にご回答ありがとうございます。
    CASE句は使ったことがなかったので、自分の見識不足を痛感しています。
    ご紹介のページを見ていると、大文字で長いコードの記述で、SQL文というよりは、COBOLプログラムを見ているような気がして、自称 昭和なプログラマー としてはほほ笑ましくなりました。
    お勧めのように他の技術も読んでいきます。

    キャンセル

checkベストアンサー

+2

SQLの種類にもよりますが、trueを1とみなすものが多いことから
普通はこの手の集計はsumでちゃちゃっとやると思います

select 部コード,sum(性別コード=1) as 男子計, sum(性別コード=2) as 女子計 ,count(*) 合計
from 部登録テーブル group by 部コード;


ポイントはカウントするのですがsumを利用すること。
条件のない合計についてはcountを利用します

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/11/01 13:32

    動作しました。ありがとうございます。
    WHERE 句で副問い合わせを使って学年別や運動部集計表や文化部集計表に使い分けたいので、シンプルなコードがありがたいです。

    キャンセル

+2

動作未確認

select 部コード,COUNT(CASE WHEN 性別コード=1 THEN 1 ELSE null END) as boy,COUNT(CASE WHEN 性別コード=2 THEN 1 ELSE null END) as girl ,count(生徒コード) as total FROM 部登録テーブル GROUP BY 部コード;


こんな感じでとれませんか?

・表示がみにくいのでSQL分を`ではなく```で囲み直しました

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/11/01 01:24

    hiimさん、早速に回答頂きありがとうございます。
    私はCASE句を今まで知りませんでした。尋ねてみるものですね。
    明日、実装環境で確かめてみます。とりあえずお礼まで。

    キャンセル

  • 2016/11/01 12:36

    動作しました!
    ありがとうございます。

    キャンセル

+1

SELECT
    部リスト.部コード,
    ISNULL(男子集計.男子数, 0) AS 男子計,
    ISNULL(女子集計.女子数, 0) AS 女子計,
    ISNULL(男子集計.男子数, 0) + ISNULL(女子集計.女子数, 0) AS 合計
FROM
    (
        SELECT
            DISTINCT 部コード
        FROM
            部登録テーブル
    ) AS 部リスト
    LEFT JOIN
        (
            SELECT
                部コード,
                COUNT(生徒コード) AS 男子数
            FROM
                部登録テーブル
            WHERE
                性別コード = 1
            GROUP BY
                部コード
        ) AS 男子集計
    ON  部リスト.部コード = 男子集計.部コード
    LEFT JOIN
        (
            SELECT
                部コード,
                COUNT(生徒コード) AS 女子数
            FROM
                部登録テーブル
            WHERE
                性別コード = 2
            GROUP BY
                部コード
        ) AS 女子集計
    ON  部リスト.部コード = 女子集計.部コード


こんな感じでしょうか。
部リストはマスタテーブルがあるならそちらを利用した方が良いでしょう。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/11/01 01:29

    nakitさん、早速に回答頂きありがとうございます。
    実は半年ほど前、LEFT JOIN を使って試行錯誤し投げ出したもので、ふと思い立ってお尋ねした次第です。LEFT JOIN を組み込むとWHERE句に部登録テーブルを記述しないので奇妙な気がした覚えがあります。
    ご指摘の通り、部マスタテーブルも存在します。それをWHERE句に適用すれば、0人の部もテーブルに表示できるのですね。
    明朝、実装環境で確かめてみます。とりあえずお礼まで。

    キャンセル

  • 2016/11/01 12:41

    ちょっとフィールド名やエイリアスを対応させるのがまだうまくいっていないので、表示されません。さらに手当てしてやってみます。

    キャンセル

+1

UNIONを使ったSQLです。

SELECT 部コード,SUM(D) AS 男子計, SUM(J) AS 女子計, SUM(G) AS 合計
FROM
(SELECT 部コード,1 AS D ,0 AS J,1 AS G FROM 部登録テーブル WHERE 性別コード = 1
UNION ALL
SELECT 部コード,0,1,1 FROM 部登録テーブル WHERE 性別コード = 2 
) TB GROUP BY 1 ORDER BY 1

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/11/01 12:36

    動作しました!
    ありがとうございます。

    キャンセル

0

DecodeがOracle方言だった場合はご容赦ください。

SELECT
部コード,
SUM(DECODE(性別コード, 1, 1, 0)) "男子計",
SUM(DECODE(性別コード, 2, 1, 0)) "女子計"
FROM 部登録テーブル
GROUP BY 部コード;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/11/01 13:20

    > DecodeがOracle方言だった場合はご容赦ください。

    MySQL にも DECODE という名前の関数はありますが、用途が異なりますね。
    https://dev.mysql.com/doc/refman/5.6/ja/encryption-functions.html#function_decode

    MySQL で同様のことを行なうには CASE 関数や IF 関数を使うか、
    https://dev.mysql.com/doc/refman/5.6/ja/control-flow-functions.html#operator_case
    https://dev.mysql.com/doc/refman/5.6/ja/control-flow-functions.html#function_if

    yambejp様の回答のように単に
     性別コード=1
    とする方法がありますね。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.64%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る