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

Question

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 を使うのでしょうが、何回か挑戦しては挫折しています。よろしくお願いします。

---

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

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

Answer 1

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

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

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

Comments

[seastar3]

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

Answer 2

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

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

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

Comments

[seastar3]

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

Answer 3

動作未確認

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分を`ではなく```で囲み直しました

Comments

[seastar3]

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

[seastar3]

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

Answer 4

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

Comments

[seastar3]

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

Answer 5

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  部リスト.部コード = 女子集計.部コード

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

Comments

[seastar3]

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

[seastar3]

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

Answer 6

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

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

Comments

[KiyoshiMotoki]

> 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
とする方法がありますね。