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

編集履歴

回答編集履歴

5

修正

2018/04/10 07:04

投稿

sazi
sazi

スコア25197

test CHANGED
@@ -4,13 +4,13 @@
4
4
 
5
5
  select `name`
6
6
 
7
- , (cnt/q1y) * 100 as q1y_per
7
+ , (q1y/cnt) * 100 as q1y_per
8
8
 
9
- , (cnt/q2y) * 100 as q2y_per
9
+ , (q2y/cnt) * 100 as q2y_per
10
10
 
11
- , (cnt/q3y) * 100 as q3y_per
11
+ , (q3y/cnt) * 100 as q3y_per
12
12
 
13
- , (cnt/q4y) * 100 as q4y_per
13
+ , (q4y/cnt) * 100 as q4y_per
14
14
 
15
15
  from (
16
16
 
@@ -44,13 +44,13 @@
44
44
 
45
45
  select `name`
46
46
 
47
- , (cnt/q1y) * 100 as q1y_per
47
+ , (q1y/cnt) * 100 as q1y_per
48
48
 
49
- , (cnt/q2y) * 100 as q2y_per
49
+ , (q2y/cnt) * 100 as q2y_per
50
50
 
51
- , (cnt/q3y) * 100 as q3y_per
51
+ , (q3y/cnt) * 100 as q3y_per
52
52
 
53
- , (cnt/q4y) * 100 as q4y_per
53
+ , (q4y/cnt) * 100 as q4y_per
54
54
 
55
55
  from (
56
56
 
@@ -80,13 +80,13 @@
80
80
 
81
81
  select `name`
82
82
 
83
- , count(*) / sum((q1='y')) * 100 as q1y_per
83
+ , sum((q1='y')) / count(*) * 100 as q1y_per
84
84
 
85
- , count(*) / sum((q2='y')) * 100 as q2y_per
85
+ , sum((q2='y')) / count(*) * 100 as q2y_per
86
86
 
87
- , count(*) / sum((q3='y')) * 100 as q3y_per
87
+ , sum((q3='y')) / count(*) * 100 as q3y_per
88
88
 
89
- , count(*) / sum((q4='y')) * 100 as q4y_per
89
+ , sum((q4='y')) / count(*) * 100 as q4y_per
90
90
 
91
91
  from ckeck
92
92
 

4

修正

2018/04/10 07:03

投稿

sazi
sazi

スコア25197

test CHANGED
@@ -2,7 +2,7 @@
2
2
 
3
3
  ```SQL
4
4
 
5
- slect `name`
5
+ select `name`
6
6
 
7
7
  , (cnt/q1y) * 100 as q1y_per
8
8
 
@@ -42,7 +42,7 @@
42
42
 
43
43
  ```SQL
44
44
 
45
- slect `name`
45
+ select `name`
46
46
 
47
47
  , (cnt/q1y) * 100 as q1y_per
48
48
 
@@ -78,7 +78,7 @@
78
78
 
79
79
  ```SQL
80
80
 
81
- slect `name`
81
+ select `name`
82
82
 
83
83
  , count(*) / sum((q1='y')) * 100 as q1y_per
84
84
 

3

推敲

2018/04/10 06:44

投稿

sazi
sazi

スコア25197

test CHANGED
@@ -80,13 +80,13 @@
80
80
 
81
81
  slect `name`
82
82
 
83
- , cout(*)/ sum((q1='y')) * 100 as q1y_per
83
+ , count(*) / sum((q1='y')) * 100 as q1y_per
84
84
 
85
- , cout(*)/ sum((q2='y')) * 100 as q2y_per
85
+ , count(*) / sum((q2='y')) * 100 as q2y_per
86
86
 
87
- , cout(*)/ sum((q3='y')) * 100 as q3y_per
87
+ , count(*) / sum((q3='y')) * 100 as q3y_per
88
88
 
89
- , cout(*)/ sum((q4='y')) * 100 as q4y_per
89
+ , count(*) / sum((q4='y')) * 100 as q4y_per
90
90
 
91
91
  from ckeck
92
92
 

2

修正

2018/04/10 06:42

投稿

sazi
sazi

スコア25197

test CHANGED
@@ -39,8 +39,6 @@
39
39
  ---
40
40
 
41
41
  boolean型を数値として扱って簡略化すると
42
-
43
- ```
44
42
 
45
43
  ```SQL
46
44
 

1

追記

2018/04/10 06:41

投稿

sazi
sazi

スコア25197

test CHANGED
@@ -33,3 +33,65 @@
33
33
  ) clc
34
34
 
35
35
  ```
36
+
37
+ 追記
38
+
39
+ ---
40
+
41
+ boolean型を数値として扱って簡略化すると
42
+
43
+ ```
44
+
45
+ ```SQL
46
+
47
+ slect `name`
48
+
49
+ , (cnt/q1y) * 100 as q1y_per
50
+
51
+ , (cnt/q2y) * 100 as q2y_per
52
+
53
+ , (cnt/q3y) * 100 as q3y_per
54
+
55
+ , (cnt/q4y) * 100 as q4y_per
56
+
57
+ from (
58
+
59
+ select `name`
60
+
61
+ , sum((q1='y')) as q1y
62
+
63
+ , sum((q2='y')) as q2y
64
+
65
+ , sum((q3='y')) as q3y
66
+
67
+ , sum((q4='y')) as q4y
68
+
69
+ , count(*) as cnt
70
+
71
+ from ckeck
72
+
73
+ group by `name`
74
+
75
+ ) clc
76
+
77
+ ```
78
+
79
+ いっそまとめて
80
+
81
+ ```SQL
82
+
83
+ slect `name`
84
+
85
+ , cout(*)/ sum((q1='y')) * 100 as q1y_per
86
+
87
+ , cout(*)/ sum((q2='y')) * 100 as q2y_per
88
+
89
+ , cout(*)/ sum((q3='y')) * 100 as q3y_per
90
+
91
+ , cout(*)/ sum((q4='y')) * 100 as q4y_per
92
+
93
+ from ckeck
94
+
95
+ group by `name`
96
+
97
+ ```