teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップMySQLに関する質問Mysql：カラム毎の割合を抽出したい

編集履歴

回答編集履歴

5

修正

2018/04/10 07:04

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -1,10 +1,10 @@
1
1
  もう少し簡略化できそうな気もしますが、取り敢えずこんな感じでしょうか。
2
2
  ```SQL
3
3
  select `name`
4
- , (cnt/q1y) * 100 as q1y_per
4
+ , (q1y/cnt) * 100 as q1y_per
5
- , (cnt/q2y) * 100 as q2y_per
5
+ , (q2y/cnt) * 100 as q2y_per
6
- , (cnt/q3y) * 100 as q3y_per
6
+ , (q3y/cnt) * 100 as q3y_per
7
- , (cnt/q4y) * 100 as q4y_per
7
+ , (q4y/cnt) * 100 as q4y_per
8
8
  from (
9
9
  select `name`
10
10
  , sum(case when q1='y' then 1 else 0 end) as q1y
@@ -21,10 +21,10 @@
21
21
  boolean型を数値として扱って簡略化すると
22
22
  ```SQL
23
23
  select `name`
24
- , (cnt/q1y) * 100 as q1y_per
24
+ , (q1y/cnt) * 100 as q1y_per
25
- , (cnt/q2y) * 100 as q2y_per
25
+ , (q2y/cnt) * 100 as q2y_per
26
- , (cnt/q3y) * 100 as q3y_per
26
+ , (q3y/cnt) * 100 as q3y_per
27
- , (cnt/q4y) * 100 as q4y_per
27
+ , (q4y/cnt) * 100 as q4y_per
28
28
  from (
29
29
  select `name`
30
30
  , sum((q1='y')) as q1y
@@ -39,10 +39,10 @@
39
39
  いっそまとめて
40
40
  ```SQL
41
41
  select `name`
42
- , count(*) / sum((q1='y')) * 100 as q1y_per
42
+ , sum((q1='y')) / count(*) * 100 as q1y_per
43
- , count(*) / sum((q2='y')) * 100 as q2y_per
43
+ , sum((q2='y')) / count(*) * 100 as q2y_per
44
- , count(*) / sum((q3='y')) * 100 as q3y_per
44
+ , sum((q3='y')) / count(*) * 100 as q3y_per
45
- , count(*) / sum((q4='y')) * 100 as q4y_per
45
+ , sum((q4='y')) / count(*) * 100 as q4y_per
46
46
  from ckeck
47
47
  group by `name`
48
48
  ```

4

修正

2018/04/10 07:03

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -1,6 +1,6 @@
1
1
  もう少し簡略化できそうな気もしますが、取り敢えずこんな感じでしょうか。
2
2
  ```SQL
3
- slect `name`
3
+ select `name`
4
4
  , (cnt/q1y) * 100 as q1y_per
5
5
  , (cnt/q2y) * 100 as q2y_per
6
6
  , (cnt/q3y) * 100 as q3y_per
@@ -20,7 +20,7 @@
20
20
  ---
21
21
  boolean型を数値として扱って簡略化すると
22
22
  ```SQL
23
- slect `name`
23
+ select `name`
24
24
  , (cnt/q1y) * 100 as q1y_per
25
25
  , (cnt/q2y) * 100 as q2y_per
26
26
  , (cnt/q3y) * 100 as q3y_per
@@ -38,7 +38,7 @@
38
38
  ```
39
39
  いっそまとめて
40
40
  ```SQL
41
- slect `name`
41
+ select `name`
42
42
  , count(*) / sum((q1='y')) * 100 as q1y_per
43
43
  , count(*) / sum((q2='y')) * 100 as q2y_per
44
44
  , count(*) / sum((q3='y')) * 100 as q3y_per

3

推敲

2018/04/10 06:44

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -39,10 +39,10 @@
39
39
  いっそまとめて
40
40
  ```SQL
41
41
  slect `name`
42
- , cout(*)/ sum((q1='y')) * 100 as q1y_per
42
+ , count(*) / sum((q1='y')) * 100 as q1y_per
43
- , cout(*)/ sum((q2='y')) * 100 as q2y_per
43
+ , count(*) / sum((q2='y')) * 100 as q2y_per
44
- , cout(*)/ sum((q3='y')) * 100 as q3y_per
44
+ , count(*) / sum((q3='y')) * 100 as q3y_per
45
- , cout(*)/ sum((q4='y')) * 100 as q4y_per
45
+ , count(*) / sum((q4='y')) * 100 as q4y_per
46
46
  from ckeck
47
47
  group by `name`
48
48
  ```

2

修正

2018/04/10 06:42

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -19,7 +19,6 @@
19
19
  追記
20
20
  ---
21
21
  boolean型を数値として扱って簡略化すると
22
- ```
23
22
  ```SQL
24
23
  slect `name`
25
24
  , (cnt/q1y) * 100 as q1y_per

1

追記

2018/04/10 06:41

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -15,4 +15,35 @@
15
15
  from ckeck
16
16
  group by `name`
17
17
  ) clc
18
+ ```
19
+ 追記
20
+ ---
21
+ boolean型を数値として扱って簡略化すると
22
+ ```
23
+ ```SQL
24
+ slect `name`
25
+ , (cnt/q1y) * 100 as q1y_per
26
+ , (cnt/q2y) * 100 as q2y_per
27
+ , (cnt/q3y) * 100 as q3y_per
28
+ , (cnt/q4y) * 100 as q4y_per
29
+ from (
30
+ select `name`
31
+ , sum((q1='y')) as q1y
32
+ , sum((q2='y')) as q2y
33
+ , sum((q3='y')) as q3y
34
+ , sum((q4='y')) as q4y
35
+ , count(*) as cnt
36
+ from ckeck
37
+ group by `name`
38
+ ) clc
39
+ ```
40
+ いっそまとめて
41
+ ```SQL
42
+ slect `name`
43
+ , cout(*)/ sum((q1='y')) * 100 as q1y_per
44
+ , cout(*)/ sum((q2='y')) * 100 as q2y_per
45
+ , cout(*)/ sum((q3='y')) * 100 as q3y_per
46
+ , cout(*)/ sum((q4='y')) * 100 as q4y_per
47
+ from ckeck
48
+ group by `name`
18
49
  ```