sqlでcount関数するけど、データ自体は全件表示したい場合

Question

###前提・実現したいこと
クラスにいる人をカウントするのと、メンバーを表示したい。

###発生している問題・エラーメッセージ

人数ではカウントできているが、全てがまとまってしまっていて、クラスに誰がいるのかわからない。

count class name
5    桜      タカハシ

###該当のソースコード

id student class 
1  タカハシ  桜 
2  スズキ   桜 
3  タナカ    桜 
4  イシバシ  梅 
5  ナカヤマ  梅

###期待する結果

count class name
3      桜    タカハシ
               スズキ
               タナカ
2      梅    イシバシ
               ナカヤマ

###試したこと

SELECT COUNT(name), class, name FROM (SELECT * FROM stu) as class

###補足情報(言語/FW/ツール等のバージョンなど)
より詳細な情報

mysql 5.6
sequel pro
mac book

Answer 1

普通やるなら以下のどちらかでしょうね
元データ

create table tbl(id int,student varchar(30),class varchar(10));
insert into tbl values(1,'タカハシ','桜'),(2,'スズキ'  ,'桜'),(3,'タナカ'  ,'桜'),(4,'イシバシ','梅'),(5,'ナカヤマ','梅');

パターン1

select class,(select count(*) from tbl as t2 where t2.class=t1.class) as count,student from tbl as t1;

パターン2

select class,count(*) as count,group_concat(student) as student from tbl group by class;

Comments

[退会済みユーザー]

そうですね。
パターン2使おうと思います。

Answer 2

同じ様な表示になるSQLを作成してみました。

select case when t1.id=t2.id then t2.cn else ' ' end cnt,
case when t1.id=t2.id then t1.class else ' ' end class,
t1.student
from tableA t1 LEFT JOIN
(select class, min(id) id, count(*) cn from tableA group by 1) t2
using(class) order by t1.id;

実行結果

cnt     class   student
3       桜      タカハシ
                スズキ
                タナカ
2       梅      イシバシ
                ナカヤマ

Comments

[退会済みユーザー]

ありがとうございます！できました!

Answer 3

MySQLなどのRDBではできません。
必ずテーブル型となります。

DBを変えても良いのであれば、理想の形はMongoDBなどのNoSQLでできます。
またはプログラム側で連想配列にするか。

無理矢理な方法としては、
1カラム全てにcountを入れる方法があります。

何をするのかによって実装を変えたほうが良いと思います。