Mysql：カラム毎の割合を抽出したい

Question

前提・実現したいこと

今回はじめての質問になります。
宜しくお願いします。

phpとMysqlを使用しアンケートを作成しています。
各クエスチョンの「ｙ」の割合を求めたいのですが
上手く抽出することができません。
いい方法があればご教授願います。

File：check
name｜q1｜q2｜q3｜q4｜
aaaa｜y｜n｜y｜y｜
bbbb｜y｜y｜n｜n｜
cccc｜n｜y｜n｜y｜
aaaa｜n｜n｜n｜y｜
bbbb｜y｜n｜y｜n｜
cccc｜y｜n｜n｜n｜
aaaa｜n｜n｜n｜n｜
bbbb｜n｜n｜n｜n｜
cccc｜y｜y｜y｜y｜
上記の様なデータで
Name毎の各クエスチョンの「ｙ」の割合を出したい
SELECT　q1, COUNT()/(SELECT COUNT()
FROM ckeck WHERE name=‘aaaa’) AS q1
FROM check WHERE name = ‘aaaa’ GROUP BY q1

にて名前「aaaa」のq1のyとnの割合を抽出する事は可能ですが、
一気にq1〜q4の「ｙ」の割合を抽出方法はないでしょうか？

例：
aaaa｜54%｜38%｜21%｜82%｜
この様な感じで抽出

お手数お掛けしますが宜しくお願いします。

Comments

[m.ts10806]

例ですがaaaaは"name"カラムですよね。出力結果に必要でしょうか？ →すみません勘違いしました。aaaaさんの割合　が必要と言うことですね。

[m.ts10806]

y,n じゃなく1,0ということであればもう少し簡単なのですけど、データは変えられませんよね？

[退会済みユーザー]

mts10806さん早速の返事ありがとうございます。　nameカラムの出力は必要になります。データformを変更すれば0,1に辺く可能です。

Answer 1

もう少し簡略化できそうな気もしますが、取り敢えずこんな感じでしょうか。

select `name`
    , (q1y/cnt) * 100 as q1y_per
    , (q2y/cnt) * 100 as q2y_per
    , (q3y/cnt) * 100 as q3y_per
    , (q4y/cnt) * 100 as q4y_per
from (
    select `name`
          , sum(case when q1='y' then 1 else 0 end) as q1y
          , sum(case when q2='y' then 1 else 0 end) as q2y
          , sum(case when q3='y' then 1 else 0 end) as q3y
          , sum(case when q4='y' then 1 else 0 end) as q4y
          , count(*) as cnt
    from  ckeck
    group by `name`
) clc

追記

boolean型を数値として扱って簡略化すると

select `name`
    , (q1y/cnt) * 100 as q1y_per
    , (q2y/cnt) * 100 as q2y_per
    , (q3y/cnt) * 100 as q3y_per
    , (q4y/cnt) * 100 as q4y_per
from (
    select `name`
          , sum((q1='y')) as q1y
          , sum((q2='y')) as q2y
          , sum((q3='y')) as q3y
          , sum((q4='y')) as q4y
          , count(*) as cnt
    from  ckeck
    group by `name`
) clc

いっそまとめて

select `name`
    , sum((q1='y')) / count(*) * 100 as q1y_per
    , sum((q2='y')) / count(*) * 100 as q2y_per
    , sum((q3='y')) / count(*) * 100 as q3y_per
    , sum((q4='y')) / count(*) * 100 as q4y_per
from  ckeck
group by `name`

Comments

[退会済みユーザー]

cnt/q1y って逆(q1y/cnt)じゃない？

[sazi]

ですね。
ご指摘ありがとうございます。
修正しました。

[退会済みユーザー]

これ、面白いですね。
参考にさせてもらいます。

[sazi]

VBAなんかでは良く使う手法です。
excelでもSUMPRODUCT()ではわりと定番的。

[退会済みユーザー]

問題なくスムーズに対応できました。
ありがとうございました。

Answer 2

すでに回答はでているようなのでwith rollupのサンプル

• 元データ

create table `check`(name varchar(20),q1 varchar(10),q2 varchar(10),q3 varchar(10),q4 varchar(10));
insert into `check` values
('aaaa','y','n','y','y'),
('bbbb','y','y','n','n'),
('cccc','n','y','n','y'),
('aaaa','n','n','n','y'),
('bbbb','y','n','y','n'),
('cccc','y','n','n','n'),
('aaaa','n','n','n','n'),
('bbbb','n','n','n','n'),
('cccc','y','y','y','y');

• 上記をname,q1ごとに集計します

select name,q1,count(*) as count
from `check` as t1 group by name,q1 with rollup

• 比率をだします

select name
,q1
,count(*) as count
,if(isnull(q1),1,count(*)/(select count(*) from `check` as t2 where t1.name=t2.name)) as ratio
from `check` as t1 group by name,q1 with rollup

• nameがaaaaでq1=yのデータを抽出

select name
,q1
,count(*) as count
,if(isnull(q1),1,count(*)/(select count(*) from `check` as t2 where t1.name=t2.name)) as ratio
from `check` as t1 group by name,q1 with rollup
having name='aaaa' and q1='y'

追記

• なんか変なので書き直し

select name,q,yn,count(*) as count
,count(*)/(select count(*) from `check` as t2 where t1.name=t2.name) as ratio
 from (
select name,'q1' as q,q1 as yn from `check`
union all select name,'q2',q2 from `check`
union all select name,'q3',q3 from `check`
union all select name,'q4',q4 from `check`
) as t1
group by name,q,yn

• 上記集計して

select name
,yn
,sum((q='q1')* ratio) as q1
,sum((q='q2')* ratio) as q2
,sum((q='q3')* ratio) as q3
,sum((q='q4')* ratio) as q4
from(
select name,q,yn,count(*) as count
,count(*)/(select count(*) from `check` as t2 where t1.name=t2.name) as ratio
 from (
select name,'q1' as q,q1 as yn from `check`
union all select name,'q2',q2 from `check`
union all select name,'q3',q3 from `check`
union all select name,'q4',q4 from `check`
) as t1
group by name,q,yn
) as t3
group by name,yn

• havingで条件をつけて抽出

select name
,yn
,sum((q='q1')* ratio) as q1
,sum((q='q2')* ratio) as q2
,sum((q='q3')* ratio) as q3
,sum((q='q4')* ratio) as q4
from(
select name,q,yn,count(*) as count
,count(*)/(select count(*) from `check` as t2 where t1.name=t2.name) as ratio
 from (
select name,'q1' as q,q1 as yn from `check`
union all select name,'q2',q2 from `check`
union all select name,'q3',q3 from `check`
union all select name,'q4',q4 from `check`
) as t1
group by name,q,yn
) as t3
group by name,yn
having name='aaaa' and yn='y'