カウント結果を１レコードの中で横に並べたい

質問

カウント結果を１レコードの中で横に並べたい

以下のテーブルを、

test_table
id　　　group　　name
1　　　　100　　　テスト１
2　　　　100　　　テスト１
3　　　　100　　　テスト１
4　　　　200　　　テスト１
5　　　　200　　　テスト１
6　　　　200　　　テスト２
7　　　　200　　　テスト２
8　　　　200　　　テスト２
9　　　　200　　　テスト２

nameごとのカウント、更に100だけのカウント、200だけのカウントといったように、
以下のように横に並べることは可能でしょうか？

name　　　合計　　100計　　　200計
テスト１　 5　　　　3　　　　　 2
テスト２　 4　　　　0　　　　　 4

まず、nameごとの合計を取得するクエリを作って、
それに、100計列、200計列の部分をサブクエリでもってきたのですが、
これで動くには動くんですが、このやり方しかないものでしょうか。

ご教示おねがいします。

SELECT t.name, COUNT(*) as '合計',
(SELECT COUNT(*) FROM test_table as t1 WHERE t1.name = t.name AND t1.group = '100' ) as '100計',
(SELECT COUNT(*) FROM test_table as t1 WHERE t1.name = t.name AND t1.group = '200' ) as '200計'
FROM test_table as t
WHERE 1
GROUP BY t.name;

回答No.2

smileeeen · ベストアンサー

groupのカラムに入る値が分かっているのであれば
case文を使うとパフォーマンスにも優れると思います。
case文を使うと条件によって出力する値を変える事ができます。

SELECT
　　name,
　　COUNT(*) AS total,
　　SUM(CASE
　　　　WHEN group='100' THEN 1
　　　　ELSE 0
　　　　END) AS count_100,
　　SUM(CASE
　　　　WHEN group='200' THEN 1
　　　　ELSE 0
　　　　END) AS count_200
FROM
　　test_table
GROUP BY
　　name;
(※上記のSQLはインデントを付けるために全角のスペースを使っています。コピペをする際は削除して下さい。)

参考URL：

http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/mysql_15.htm

お礼

1万件レコードがあった時に10秒くらいかかってたのが0.1秒になりました！
こういった方法というか発送もあるんですね。ありがとうございます！

回答No.1

yambejp

ピボットテーブルを使うやり方があります。

create temporary table temp (num int,`合計` int,`100計` int,`200計` int);
insert into temp values(100,1,1,0),(200,1,0,1);
select name,sum(合計*c) as 合計,sum(100計*c) as 100計,sum(200計*c) as 200計
from temp inner join
(select `group`,name,count(*) as c from test_table group by `group`,name) as sub
on `group`=num group by name;

※groupは予約語です、なるべく使うのを避けてください

お礼

ありがとうございます。参考になりました。
ただ、テンポラリ作る方法は、あとあとのメンテで分かりづらくなってしまうなぁ。